String replacement 4

[DavidJA]

Hey all,

I have a problem

I need to replace all chars in @MyVarCharString that are NOT in A..Z a..z 0..9 with the '-' char.

EG:

121 O'brian@^A should equal 121-O-brian--A

Can someone please show me how to do this in T-SQL.

 

[EzLogic]

Try using the ASCII Values... (65 = A, 66=B) and so on

you can include a funciton that will check if the asc(str) is between (65 and 92) i think 92 is 'z', you can double check that! AK

 

[williamu]

Hi,

What you have here is a prime candidate for a regular expression. To help you more I'd need to know what language you are programming in.


William.

 

[tlbroadbent]

William,

It appears that the language used is T-SQL.

There is no built in command that will check the entire string or column. A user defined function in SQL 2000 could be created. In earlier versions or SQL Server, a stored procedure could be created. Another alternative is to create an extended stored procedure.

Here is some sample code for a SP.

Alter Procedure ReplaceSpecialCharacters
@param1 varchar(40),
@param2 varchar(40) OUTPUT
As
declare @c int, @s char(1)
select @c=1, @param2=@param1
While @c<=len(@param2)
Begin
Select @s=Substring(@param2,@c,1)

If Not (@s Between 'A' And 'Z'
Or @s Between'a' And 'z'
Or @s Between '0' And '9')
Set @param2=Stuff(@param2,@c,1,'-')

Select @c=@c+1
End
return Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.

 

[williamu]

Oops some confusion here. What I meant to say was the language used to interact with the SP. In other word is VB or VC++ being used to execute the Stored Procedure.

If either VB or C++ (or Perl, Java, etc.) was being used then the user simply uses a RegEx on the data before / after execution of the SP.

Or have I completly misunderstood?

William
Software Engineer

 

[clapag22]

This will do it...

declare @mystrvar varchar(1000),
@patindex int,
@replace_char char(1)

select @mystrvar = 'ABC!=+123'

select @patindex = patindex('%[^0-Z-]%', @mystrvar)

while @patindex > 0
begin

select @replace_char = substring(@mystrvar, @patindex, 1)
select @mystrvar = replace(@mystrvar, @replace_char, '-')
select @patindex = patindex('%[^0-Z-]%', @mystrvar)

end

print @mystrvar

 

[tlbroadbent]

clapag22,

Excellent code! It could easily be converted to a UDF.

Have another star! Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.

 

[clapag22]

Thanks!

Craig

 

[Patrick2002]

Hi, I had a similar solution, that makes use of more or less the same code in a function :

CREATE FUNCTION dbo.fn_clean (@my_string varchar(40))

RETURNS varchar(40)
AS
BEGIN

declare @pos smallint
declare @char char(1)

select @pos = 1
while @pos <= len(@my_string)
begin
if substring(@my_string,@pos,1) like '[A-Z]'
or substring(@my_string,@pos,1) like'[a-z]'
or substring(@my_string,@pos,1) like '[0-9]'
select @pos = @pos + 1
else
begin
select @my_string = stuff (@my_string,@pos,1,'-')
select @pos = @pos + 1
end
end

RETURN (@my_string)
END

Depending on the case, a function might be easier for you to use. Ciao,

Patrick