[sql] Remove numbers from string sql server

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.

This question is related to sql sql-server sql-server-2008

The answer is


Try below for your query. where val is your string or column name.

CASE WHEN PATINDEX('%[a-z]%', REVERSE(val)) > 1
                THEN LEFT(val, LEN(val) - PATINDEX('%[a-z]%', REVERSE(val)) + 1)
            ELSE '' END

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


Quoting part of @Jatin answer with some modifications,

use this in your where statement:

    SELECT * FROM .... etc.
        Where 
         REPLACE
        (REPLACE
        (REPLACE
        (REPLACE
        (REPLACE
        (REPLACE
        (REPLACE
        (REPLACE
        (REPLACE
        (REPLACE (Name, '0', ''),
        '1', ''),
        '2', ''),
        '3', ''),
        '4', ''),
        '5', ''),
        '6', ''),
        '7', ''),
        '8', ''),
        '9', '') = P_SEARCH_KEY

Remove everything after first digit (was adequate for my use case): LEFT(field,PATINDEX('%[0-9]%',field+'0')-1)

Remove trailing digits: LEFT(field,len(field)+1-PATINDEX('%[^0-9]%',reverse('0'+field))


Not tested, but you can do something like this:

Create Function dbo.AlphasOnly(@s as varchar(max)) Returns varchar(max) As
Begin
  Declare @Pos int = 1
  Declare @Ret varchar(max) = null
  If @s Is Not Null
  Begin
    Set @Ret = ''
    While @Pos <= Len(@s)
    Begin
      If SubString(@s, @Pos, 1) Like '[A-Za-z]'
      Begin
        Set @Ret = @Ret + SubString(@s, @Pos, 1)
      End
      Set @Pos = @Pos + 1
    End
  End
  Return @Ret
End

The key is to use this as a computed column and index it. It doesn't really matter how fast you make this function if the database has to execute it against every row in your large table every time you run the query.


One more approach using Recursive CTE..

declare @string varchar(100)
set @string ='te165st1230004616161616'

;With cte
as
(
select @string as string,0  as n
union all
select cast(replace(string,n,'') as varchar(100)),n+1
from cte
where n<9
)
select top 1 string from cte
order by n desc


**Output:**   
  test

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

Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to sql-server

Passing multiple values for same variable in stored procedure SQL permissions for roles Count the Number of Tables in a SQL Server Database Visual Studio 2017 does not have Business Intelligence Integration Services/Projects ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database How to create temp table using Create statement in SQL Server? SQL Query Where Date = Today Minus 7 Days How do I pass a list as a parameter in a stored procedure? SQL Server date format yyyymmdd

Examples related to sql-server-2008

Violation of PRIMARY KEY constraint. Cannot insert duplicate key in object How to Use Multiple Columns in Partition By And Ensure No Duplicate Row is Returned SQL Server : How to test if a string has only digit characters Conversion of a varchar data type to a datetime data type resulted in an out-of-range value in SQL query Get last 30 day records from today date in SQL Server How to subtract 30 days from the current date using SQL Server Calculate time difference in minutes in SQL Server SQL Connection Error: System.Data.SqlClient.SqlException (0x80131904) SQL Server Service not available in service list after installation of SQL Server Management Studio How to delete large data of table in SQL without log?