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

User · Answer

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 lt 9   select top 1 string from cte order by n desc     Output         test

User · Answer

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

User · Answer

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   gt  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

User · Answer

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

User · Answer

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

User · Answer

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  lt   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

User · Answer

Try below for your query  where val is your string or column name    CASE WHEN PATINDEX    a-z     REVERSE val    gt  1                 THEN LEFT val  LEN val  - PATINDEX    a-z     REVERSE val     1              ELSE    END

[sql] Remove numbers from string sql server

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2008