SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Replace Special Characters
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

SQL1234
Starting Member

3 Posts

Posted - 10/20/2005 :  11:05:21  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 10/20/2005 :  12:57:40  Show Profile  Visit Seventhnight's Homepage  Reply with Quote

/*********************************
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')


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 ..."

Edited by - Seventhnight on 10/20/2005 12:58:02
Go to Top of Page

Luuk123
Starting Member

49 Posts

Posted - 11/07/2012 :  12:36:53  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000