SQL-Removing numbers in a string
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)
ELSE Topic END
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)
AS
Begin
Declare @Range as varchar(40) = '%[0-9]%'
While PatIndex(@Range, @Temp) > 0
Set @Num = Stuff(@Temp, PatIndex(@Range, @Num), 1,