Correct use of REPLACE? 1

[Neil Toulouse]

Hi

I am trying to clean up some code, and I came across this:

SET @lnMax = ( SELECT TOP 1 [Sequence]+1 FROM Uname WHERE  
LTRIM(RTRIM(LOWER(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE([UserName],'1',''),
'2',''),'3',''),'4',''),'5',''),'6',''),'7',''),'8',''),'9',''),'0','')))) =  
LTRIM(RTRIM(LOWER(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@lcUserName,'1',''),'2',''),
'3',''),'4',''),'5',''),'6',''),'7',''),'8',''),'9',''),'0','')))) ORDER BY Sequence DESC )

...which looks a bit of a mess to me!

Basically what it is trying to do is remove numbers from the field/vairable to find a match. For example:

fat.slug23
fat.slug3456

...the code will 'remove' the numbers so it just matches against 'fat.slug'.

Is there a better (more efficient?) way of achieving the same thing?

TIA
Neil

I like work. It fascinates me. I can sit and look at it for hours...

 

[bborissov]

Check

http://stackoverflow.com/questions/...phabetic-characters-from-string-in-sql-server

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[Neil Toulouse]

Thanks Borislav, looks like that could be the one.

However, how do I change it so I can keep the '.'?

ie, at the moment if I send the function 'fat.slug123' it returns 'fatslug' but I need it to return 'fat.slug'.

TIA
Neil

I like work. It fascinates me. I can sit and look at it for hours...

 

[djj55]

I just added the "." as

SELECT dbo.fn_StripCharacters('fat.slug123', '^a-z[b].[/b]')

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

 

[Neil Toulouse]

Nice one, djj55, that will do nicely

I like work. It fascinates me. I can sit and look at it for hours...

 

[gmmastros]

It's good to know that I'm helping people even when I'm not around to post.

Notice that there are multiple differences between the function I wrote and the follow-up post that parameterizes my function. Obviously, another parameter was added to the function, but there is another (more subtle) difference. My original function accepted an input parameter for the data that was defined as varchar(1000), but the follow-up function "fn_StripCharacters" uses varchar(max).

When you use varchar(max) for a parameter, the function will perform a little slower. If you don't need max, then use a real size (like 1000 or 8000). Of course, if you are dealing with varchar(max) column to begin with, then use the varchar(max) version.

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