Saturday, February 25, 2012

Removing characters

I want to remove all non-letter, non-number characters in a string. An example string would be: How#can*I^make this@.work?
I would like it to look like HowcanImakethiswork
I need to do this for a whole field. Any ideas?
ThanksCreate a user-defined function such as this:

create function CharOnly(@.TargetString varchar(500))
returns varchar(500)
as
begin
declare @.NewString varchar(50)
declare @.Counter int
set @.NewString = ''
set @.Counter = 0
while @.Counter < len(@.TargetString)
begin
set @.Counter = @.Counter + 1
if ASCII(UPPER(substring(@.TargetString, @.Counter, 1))) between 65 and 90 set @.NewString = @.NewString + substring(@.TargetString, @.Counter, 1)
end
return @.NewString
end

Then use it in your select statement like this:
select dbo.CharOnly('How#can*I^make this@.work?')

blindman|||If you are looking for a way to process regular expressions, click on the following link:

link (http://www.codeproject.com/database/xp_pcre.asp?target=xp_pcre)

No comments:

Post a Comment