October 28, 2010 at 2:10 pm
I recently had to replace ASCII strings in a table. Being a SQL server newbie, I struggled with writing a WHILE LOOP to iterate through it. Here's the really really long replace code I used in case anyone runs into the same problem and wants an easy to code way out. Its long but it just does the same thing over and over again - replaces characters in the strings inside each set of the brackets. Took me a lot less time to write this (about an hour) than to figure out how to get the WHILE LOOP code going and I'll never have to do this again so I'm happy I did it this way. Obviously if you're going to use it and execution time is important for you you'll have to test it against alternatives.
Note that the outermost 2 replaces remove the characters # and & because the strings I was working on had each ASCII code separated by &#.
Many thanks to Peter Freitag whose cheat sheet here I used to find all the codes: http://www.petefreitag.com/cheatsheets/ascii-codes/
Note the order of the replacement matters - the longer strings have to be in the innermost replaces, otherwise stuff gets replaced twice (the Ascii gets replaced once, then what you replaced it with gets replaced again).
There are two ASCII codes that this code doesn't convert, that didn't matter for me but if it matters for you then this code isn't for you:
9 (Tab) because if I replaced that at the end, it replaced some of the replacements!
152 (squiggly thingy) because sql gave me a syntax error when I tried to convert that and it wouldn't copy in to my query in SSMS (even when I saved it as Unicode).
-- REPLACE ASCII CHARACTERS WITH THE CHARACTERS THEY REPRESENT
DECLARE @AsciiString nvarchar(4000)
SET @AsciiString = '#80rivate'
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
@AsciiString -- Change this to a column name if you're doing this for a table
,100,'d'
),101,'e'
),102,'f'
),103,'g'
),104,'h'
),105,'i'
),106,'j'
),107,'k'
),108,'l'
),109,'m'
),110,'n'
),111,'o'
),112,'p'
),113,'q'
),114,'r'
),115,'s'
),116,'t'
),117,'u'
),118,'v'
),119,'w'
),120,'x'
),121,'y'
),122,'z'
),123 ,'{'
),124 ,'|'
),125 ,'}'
),126 ,'~'
-- Extended ASCII characters start here
),192 , 'À'
),193 ,'Á'
),194 ,'Â'
),195 ,'Ã'
),196 ,'Ä'
),197 ,'Å'
),198 ,'Æ'
),199 ,'Ç'
),200 ,'È'
),201 ,'É'
),202 ,'Ê'
),203 ,'Ë'
),204 ,'Ì'
),205 ,'Í'
),206 ,'Î'
),207 ,'Ï'
),208 ,'Ð'
),209 ,'Ñ'
),210 ,'Ò'
),211 ,'Ó'
),212 ,'Ô'
),213 ,'Õ'
),214 ,'Ö'
),215 ,'×'
),216 ,'Ø'
),217 ,'Ù'
),218 ,'Ú'
),219 ,'Û'
),220 ,'Ü'
),221 ,'Ý'
),222 ,'Þ'
),223 ,'ß'
),224 ,'à'
),225 ,'á'
),226 ,'â'
),227 ,'ã'
),228 ,'ä'
),229 ,'å'
),230 ,'æ'
),231 ,'ç'
),232 ,'è'
),233 ,'é'
),234 ,'ê'
),235 ,'ë'
),236 ,'ì'
),237 ,'í'
),238 ,'î'
),239 ,'ï'
),240 ,'ð'
),241 ,'ñ'
),242 ,'ò'
),243 ,'ó'
),244 ,'ô'
),245 ,'õ'
),246 ,'ö'
),247 ,'÷'
),248 ,'ø'
),249 ,'ù'
),250 ,'ú'
),251 ,'û'
),252 ,'ü'
),253 ,'ý'
),128,'€'
),129,'�'
),130,'‚'
),131,'ƒ'
),132,'„'
),133,'…'
),134,'†'
),135,'‡'
),136,'ˆ'
),137,'‰'
),138,'Š'
),139,'‹'
),140,'Œ'
),141,'�'
),142,'Ž'
),143,'�'
),144,'�'
),145,'‘'
),146,'’'
),147,'“'
),148,'”'
),149,'•'
),150,'–'
),151,'—'
),153,'™'
),154,'š'
),155,'›'
),156,'œ'
),157,'�'
),158,'ž'
),159,'Ÿ'
),160,' '
),161,'¡'
),162,'¢'
),163,'£'
),164,'¤'
),165,'¥'
),166,'¦'
),167,'§'
),168,'¨'
),169,'©'
),170,'ª'
),171,'«'
),172,'¬'
),173,''
),174,'®'
),175,'¯'
),176,'°'
),177,'±'
),178,'²'
),179,'³'
),180,'´'
),181,'µ'
),182,'¶'
),183,'·'
),184,'¸'
),185,'¹'
),186,'º'
),187,'»'
),188,'¼'
),189,'½'
),190,'¾'
),191,'¿'
-- Extended ASCII characters finish here
),10, CHAR(10) --Newline
),13 ,CHAR(13) --Carraige return
),32,' '
),65,'A'
),66,'B'
),67,'C'
),68,'D'
),69,'E'
),70,'F'
),71,'G'
),72,'H'
),73,'I'
),74,'J'
),75,'K'
),76,'L'
),77,'M'
),78,'N'
),79,'O'
),80,'P'
),81,'Q'
),82,'R'
),83,'S'
),84,'T'
),85,'U'
),86,'V'
),87,'W'
),88,'X'
),89,'Y'
),90,'Z'
),97,'a'
),98,'b'
),99,'c'
),48 ,'0'
),49 ,'1'
),50 ,'2'
),51 ,'3'
),52 ,'4'
),53 ,'5'
),54 ,'6'
),55 ,'7'
),56 ,'8'
),57 ,'9'
),10 ,CHAR(10) -- (NL)
),13 ,CHAR(13) --\r (CR)
),32,' ' --Space
),33 ,'!'
),34 ,'"'
),35 ,'#'
),36 ,'$'
),37 ,'%'
),38 ,'&'
),39 ,''''
),40 ,'('
),41 ,')'
),42 ,'*'
),43 ,'+'
),44 ,','
),45 ,'-'
),46 ,'.'
),47 ,'/'
),48 ,'0'
),49 ,'1'
),50 ,'2'
),51 ,'3'
),52 ,'4'
),53 ,'5'
),54 ,'6'
),55 ,'7'
),56 ,'8'
),57 ,'9'
),58 ,':'
),59 ,';'
),60 ,'<'
),61 ,'='
),62 ,'>'
),63 ,'?'
),64 ,'@'
),91 ,'['
),92 ,'\'
),93 ,']'
),94 ,'^'
),95 ,'_'
),96 ,'`'
),'#',''
),'&',''
)
October 28, 2010 at 3:59 pm
Thanks Anyway, but I will stick to using the ASCII function that comes with SQL.
You could have also used a tally table.
SQL Server is not a Cursor based database, It is a Set based database. It works much better with sets of data, as opposed to looping and cursors thru like Oracle.
There is even a great example in the SQL BOL that does almost exactly what you did in all that awful code.
Andrew SQLDBA
November 3, 2010 at 3:01 am
Andrew - way to be supportive of someone making one of their first posts in the newbie forum.
I acknowledged in my post that there are better ways of doing this, but this is easy to understand and if speed doesn't matter and it gets the job done - why do you care if the code is 'awful'? I'd prefer to waste a computer's time doing unnecessary processing than a human's doing unnecessary learning when the opportunity cost of my time is very high (db programming isn't my area but I am have a reasonable level of expertise in the area that I work in) and the opportunity cost of the computer's time is very low (it'll just be sitting idle and I don't need this output to be returned quickly).
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply