Eliminating characters in a field

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

SQL Server Development (2000)

Eliminating characters in a field

Author

Topic

DrewBurlingame
Starting Member

49 Posts

Posted - 2002-02-06 : 18:07:49

Our product codes often have special characters like '*' or '/' or '-' in them. I need a function that will take all the special characters out of the code for a select statement. Is there a function like this in SQL Server?

Thanks,
Drew

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-02-06 : 18:15:04

Drew,

Look up REPLACE in BOL

HTH

DavidM

Tomorrow is the same day as Today was the day before.

Tigger
Yak Posting Veteran

85 Posts

Posted - 2002-02-06 : 20:29:33

If you need to remove a lot of different characters you can try using the following function which was written by Arnold Fribble in reply to a similar problem I had:

CREATE FUNCTION RemoveChars (@Str varchar(8000), @DelPat varchar(8000))
RETURNS varchar(8000)
AS
BEGIN
DECLARE @Res varchar(8000), @i int

SET @DelPat = '%[' + @DelPat + ']%'

SET @Res = ''

SET @i = PATINDEX(@DelPat, @Str)

WHILE @i <> 0
BEGIN
SET @Res = @Res + SUBSTRING(@Str, 1, @i - 1)
SET @Str = SUBSTRING(@Str, @i + 1, 8000)
SET @i = PATINDEX(@DelPat, @Str)
END

SET @Res = @Res + @Str
RETURN @Res
END

To use the function you can use an update query.

UPDATE TableName
SET Col1 = RemoveChars(Col1, '^ A-Z0-9')

'^ A-Z0-9' tells the function to remove all chars which are NOT (^) space, A-Z, 0-9. To remove different characters you just change the characters passed to the function ie to delete all A's it would be
RemoveChars(Col1, 'A')

I've used the function loads of times and because you pass the values to be removed you can even use the same function to delete different characters from different columns in the same update statement.

DrewBurlingame
Starting Member

49 Posts

Posted - 2002-02-07 : 02:05:04

RemoveChars was exactly what I needed. You are my hero. Thanks Tigger and Arnold.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources