Remove numbers from string sql server

Question

I have a large database in which I want to do a part string search. The user will enter characters: JoeBloggs.

For arguments sake if I had a name Joe 23 Blo Ggs 4 in the database. I want to remove everything in the name other than A-Z.

I have the REPLACE(Name, ' ','') function to remove spaces and the UPPER() function to capitalize the name.

Is there a more efficient fast way maybe by terms of regex to replace anything other than A-Z. I cannot change the values in the database.

Answer 1

1st option -

You can nest REPLACE() functions up to 32 levels deep. It runs fast.

REPLACE (REPLACE (REPLACE (REPLACE (REPLACE (REPLACE (REPLACE (REPLACE (REPLACE (REPLACE (@str, '0', ''), '1', ''), '2', ''), '3', ''), '4', ''), '5', ''), '6', ''), '7', ''), '8', ''), '9', '') 

2nd option -- do the reverse of -

Removing nonnumerical data out of a number + SQL

3rd option - if you want to use regex

then http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=27205

Answer 2

This one works for me

CREATE Function [dbo].[RemoveNumericCharacters](@Temp VarChar(1000)) Returns VarChar(1000) AS Begin      Declare @NumRange as varchar(50) = '%[0-9]%'     While PatIndex(@NumRange, @Temp) > 0         Set @Temp = Stuff(@Temp, PatIndex(@NumRange, @Temp), 1, '')      Return @Temp End 

and you can use it like so

SELECT dbo.[RemoveNumericCharacters](Name) FROM TARGET_TABLE