Function removes unwanted characters AND spaces

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 2005 Forums

Transact-SQL (2005)

Function removes unwanted characters AND spaces

Author

Topic

Abu-Dina
Posting Yak Master

206 Posts

Posted - 2012-08-10 : 07:59:14

Hi,

I found this clever function which is great but if I want to remove.

It's a slick way of removing the unwanted characters but how do I keep the white spaces?! (see example of how I'm using it below)


alter Function [dbo].[RemoveNonAlphaCharacters](@Temp VarChar(1000)) 
Returns VarChar(1000) 
AS 
Begin 
 
    While PatIndex('%[^a-z]%', @Temp) > 0 
        Set @Temp = Stuff(@Temp, PatIndex('%[^a-z]%', @Temp), 1, '') 
 
    Return @Temp
End

select [dbo].[RemoveNonAlphaCharacters]('the>cat :, sat{{{}on the}? mat```')

I understand how the function works but I'm not seeing how I get it to ignore white spaces. I've tried various method but all I've managed to do so far is get the function to run an infinite loop!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-08-10 : 08:39:09

[code]alter Function [dbo].[RemoveNonAlphaCharacters](@Temp VarChar(1000))
Returns VarChar(1000)
AS
Begin

While PatIndex('%[^a-z ]%', @Temp) > 0
Set @Temp = Stuff(@Temp, PatIndex('%[^a-z ]%', @Temp), 1, '')

Return @Temp
End
go
select [dbo].[RemoveNonAlphaCharacters]('the>cat :, sat{{{}on the}? mat```')[/code]

N 56°04'39.26"
E 12°55'05.63"

Abu-Dina
Posting Yak Master

206 Posts

Posted - 2012-08-10 : 09:06:34

Thanks Peso ;)

Abu-Dina
Posting Yak Master

206 Posts

Posted - 2012-08-10 : 09:29:36

It's almost doing what I want it to do BUT


alter Function [dbo].[RemoveNonAlphaCharacters](@Temp VarChar(1000)) 
Returns VarChar(1000) 
AS 
Begin 
 
    While PatIndex('%[^a-z - '']%', @Temp) > 0 
        Set @Temp = Stuff(@Temp, PatIndex('%[^a-z - '']%', @Temp), 1, '') 
 
    Return @Temp
End
go

[code]
select [dbo].[RemoveNonAlphaCharacters](' O''Neil ??/ OMG!!!!! %$See<> No,,,,, See.... see-his ')[code]

Gives back: O'Neil OMG See No See seehis

I've retained the space and the single quote but I can't keeop the dash!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources