Click here to check my latest exciting videos on youtube
Search Mallstuffs

Flag Counter
Spirituality, Knowledge and Entertainment

Locations of visitors to this page

Latest Articles

Move to top
SQL-Removing numbers in a string
Posted By Sarin on Apr 04, 2013     RSS Feeds     Latest Hinduism news

Recently, I got the requirement of removing the trailing integers in a string. This was not difficult as I had the readymade SQL function to achieve the same (Which I have used in my previous company). But the problem was I had to query the server where I did not have permissions to create functions. In this case, my readymade function was not going to help. So, I decided to write a query instead of SP or function to achieve the same. The query which I used was

Using PATINDEX function
SELECT CASE WHEN PATINDEX('%[0-9]%', ColumnName)>1 THEN LEFT(ColumnName,PATINDEX('%[0-9]%', ColumnName)-1)
ELSE ColumnName END AliasName from TableName
As an example, you can refer the below screen to see the result of this operation in the second column

If you want to do the same in update query, you can use the following code. I have used PATINDEX to search for the first occurrence of numbers (between 0 to 9) and then use the left function to get the string left to the number.

UPDATE TableName
SET Topic=CASE WHEN PATINDEX('%[0-9]%',Topic)>1 THEN LEFT(Topic,PATINDEX('%[0-9]%',Topic)-1)
Using Replace function
Alternatively, you can use the following query which search for each numerals (0 to 10) in a string one at a time and if found, replace it with empty string.
select REPLACE  (REPLACE  (REPLACE  (REPLACE  (REPLACE  (REPLACE  (REPLACE  (REPLACE  (REPLACE  (REPLACE  (ColumnName, '0', ''), '1', ''), '2', ''), '3', ''), '4', ''), '5', ''), '6', ''), '7', ''), '8', ''), '9', '')
from TableName

Creating custom function
If you have the permissions to create function then use the below function to achieve this functionality

CREATE Function [dbo].[RemoveNumerals](@Num VarChar(40))
Returns VarChar(40)
    Declare @Range as varchar(40) = '%[0-9]%'
    While PatIndex(@Range, @Temp) > 0
        Set @Num = Stuff(@Temp, PatIndex(@Range, @Num), 1, '')
    Return @Num

Use the below query to get the result string
SELECT dbo.[RemoveNumerals](Name) FROM TARGET_TABLE
In this article, we saw the various ways of removing numbers from the string. Hope this helps you in your requirement.
Note: Images used on this website are either a production of Bhaktivedanta Book Trust(, Iskcon Foundation or were found in google search under "Free to use and share". If any of the images presented here violates copyright issues or infringes anyone copyright or are not under "Fair use", then please bring it to our notice. Read Disclaimer for more.

Share this to your friends. One of your friend is waiting for your share.
Related Articles
Generating unique random numbers and date
Science in Hinduism-Place value and Decimal number system
Different string functions in SQL-Part 2
Converting Fraction into decimal
Advanced Strings Format for numbers
Advanced Datetime Format for string
Ways of generating random password or string
Different Strings Format for Date Time
Commonly used string Functions
Parse comma separated input string in SQL

Post Comment