Instead of Replace

[Ringers]

Hi all,

I have used replace to remove unwanted character in my join. It works and get the results I want but is there a more elegant or efficent way to do it?
[SQL]
select *
from xxxx.yyyy s
inner join xxx.vvvvvv e
on s.member_number = Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(admin_member_id,'a',''),'k',''),'s',''),'b',''),'l',''),'m',''),'I',''),'c',''),'q','')
[/SQL]

 

[markros]

Apart from creating a function (may be CLR) there is no better method in T-SQL.

PluralSight Learning Library

 

[djj55]

Ringers, Thank you for the [SQL] try. What you are looking for is code. See Process TGML in Step 2 Options under Your Replay.



djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[gmmastros]

I wrote a function to strip out non-alpha characters from a string of data. I posted this on Stack Overflow, and then Even Mien parameterized it. The Stack Overflow post is here:

http://stackoverflow.com/questions/...ers-from-string-in-sql-server/1008566#1008566

The function:

CREATE FUNCTION [dbo].[fn_StripCharacters]
(
    @String NVARCHAR(MAX), 
    @MatchExpression VARCHAR(255)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    SET @MatchExpression =  '%['+@MatchExpression+']%'

    WHILE PatIndex(@MatchExpression, @String) > 0
        SET @String = Stuff(@String, PatIndex(@MatchExpression, @String), 1, '')

    RETURN @String

END

You would then use it like this:

select *
from   xxxx.yyyy s
       inner join xxx.vvvvvv e
         on s.member_number = dbo.fn_StripCharacters(admin_member_id,'aksblmIcq')

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom