Wsrtjtyk

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.

Thanks in advance

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

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

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

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

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

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.