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)
Saturday, February 25, 2012
Removing characters
Labels:
characters,
database,
example,
howcanimake,
microsoft,
mysql,
non-letter,
non-number,
oracle,
removing,
server,
sql,
string,
thisworki
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment