Replace Special Characters - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Replace Special Characters

Author

Topic

SQL1234
Starting Member

3 Posts

Posted - 2005-10-20 : 11:05:21

I need to strip out all non letter/number characters from a series of strings in a query. Is there a better way to do this than a bunch of replace functions?

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-10-20 : 12:57:40

[code]
/*********************************
Removes any characters from
@myString that do not meet the
provided criteria.
*********************************/
CREATE FUNCTION dbo.GetCharacters(@myString varchar(500), @validChars varchar(100))
RETURNS varchar(500) AS
BEGIN

While @myString like '%[^' + @validChars + ']%'
Select @myString = replace(@myString,substring(@myString,patindex('%[^' + @validChars + ']%',@myString),1),'')

Return @myString
END
Go

Declare @testStr varchar(1000),
@i int

Set @i = 1
while @i < 255
Select
@TestStr = isnull(@TestStr,'') + isnull(char(@i),''),
@i = @i + 1

Select @TestStr
Select dbo.GetCharacters(@TestStr,'a-z')
Select dbo.GetCharacters(@TestStr,'0-9')
Select dbo.GetCharacters(@TestStr,'0-9a-z')
Select dbo.GetCharacters(@TestStr,'02468bferlki')
[/code]

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."

Luuk123
Yak Posting Veteran

52 Posts

Posted - 2012-11-07 : 12:36:53

Thank you for your reaction.
Though i've used the following query:

While @myString like '%[^' + @validChars + ']%'
		Select @myString = replace(@myString,substring(@myString,patindex('%[^' + @validChars + ']%',@myString),1),'')

	Return @myString

Thanks to Seventhnight

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources