How to get other than numerics in a string(removing numerics in strings) in sqlserver

Question

how to truncate the numerics in a string in sqlserver

ForEx: String value: 200 May not be[34534]

Result: Many not be (reulst contains other than numerics)

Answer 1

If you want to strip out all numbers from a string, there are various potential ways:

1) pure TSQL:

e.g. (not necessarily the best/prettiest way)

DECLARE @Test VARCHAR(50)
SET @Test = 'Test12value8with numbers'

DECLARE @index INTEGER
SELECT @index = PATINDEX('%[0-9]%', @Test)

WHILE (@index > 0)
    BEGIN
        SET @Test = LEFT(@Test, @index - 1) + RIGHT (@Test, (LEN(@Test) - @index))
        SELECT @index = PATINDEX('%[0-9]%', @Test)
    END

SELECT @Test

2) CLR functionality (SQL 2005+) - create a CLR function to do the replace (i.e. in .NET), and call that function

3) don't do the manipulation in SQL at all, return the data as-is and leave the formatting/cleaning up to the calling code (that will perform better at string manipulation).

The 3rd option would be the one I would naturally go for, unless there's a good reason not to.

Answer 2

SQL Server doesn't natively support regular expressions in the REPLACE() function. Have you tried one of these approaches:

1. Leverage .NET regex support by integrating SQL Server and the CLR
2. Write a stored procedure or user defined function that loops through the string and uses PATINDEX() to locate the numbers (PATINDEX lets you specify a pattern to find, REPLACE does not)

Both of these have some performance implications, so I'd be careful in how you use them.

Answer 3

I wrote a blog about this several months ago.

Extracting Data

The method shown in the blog is very similar to the Pure T-SQL method suggested by AdaTheDev.