String special character conversion

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

Transact-SQL (2000)

String special character conversion

Author

Topic

cherry
Starting Member

6 Posts

Posted - 2005-01-25 : 05:13:50

Hi,

I'd like to do some conversion with strings and special characters.
I have a table containing a field with this or similar content: ]D[ N;5__
and another table with something like: %5DD%5B%20%20%20N%3B5%5F%5F%20
The two strings match - the second one is some sort of Hex escape code for special chars (I assume).
Is there a way to easily convert them in order to match them in an SQL statement?

Thanks in advance

cherry~

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-01-25 : 05:51:08

You can update the field using REPLACE

Does this help
DECLARE @String varchar(50)
SET @String = '%5DD%5B%20%20%20N%3B5%5F%5F%20'

SELECT @String AS Original

SET @String = REPLACE(@String,'%5D',']')
SET @String = REPLACE(@String,'%20',' ')
SET @String = REPLACE(@String,'%5B','[')
SET @String = REPLACE(@String,'%3B',';')
SET @String = REPLACE(@String,'%5F','_')

SELECT @String AS Converted

Andy

cherry
Starting Member

6 Posts

Posted - 2005-01-25 : 07:54:05

quote:
Originally posted by AndyB13

You can update the field using REPLACE

Yes that would be an option. But I thought SQL Server already has a function implemented to perform the given task.
The problem with your solution is that ] and [ are not the chars to be replaced there are about 40 of them which makes the solution a bit ugly to code and execute.
But thanks I'll try this if I won't be able to come up with something else.

Any other suggestions?

cherry~

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-01-25 : 09:56:44

Ideally, whatever application generated the coded text and populated the table should have functions to code and decode these values. If you could get the decoded values into your table, problem solved. However if that is not an option for you, you'll probably have to get a little creative. Here are some built-in sql functions that might prove helpfull in that effort:

-- to convert from '%5D' to '0x5D'
Select replace('%5D', '%', '0x')

--to convert from '0x5d' to ']'
--(char() imlicitly converts the binary argument to int)
Select char(0x5D)

-- to convert binary(1) '0x5D' to int '93'
Select convert(int, 0x5D)

--to convert int '93' to character ']'
Select char(93)

-- to convert character ']' to int '93'
Select ascii(']')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources