How can I see all the "special" characters permissible in a varchar or char field in SQL Server? [closed]

Question

Where can I see all the non-visible characters stored on a varchar or char field in SQL Server? e.g. \n (new-line), \r (carriage return) and other "non-printable" characters?

How to print them (as they are not printable but perform some action on the text)?

Answer 1

You probably just need to see the ASCII and EXTENDED ASCII character sets. As far as I know any of these are allowed in a char/varchar field.

If you use nchar/nvarchar then it's pretty much any character in any unicode set in the world.

Answer 2

EDIT based on comments:

If you have line breaks in your result set and want to remove them, make your query this way:

SELECT     REPLACE(REPLACE(YourColumn1,CHAR(13),' '),CHAR(10),' ')    ,REPLACE(REPLACE(YourColumn2,CHAR(13),' '),CHAR(10),' ')    ,REPLACE(REPLACE(YourColumn3,CHAR(13),' '),CHAR(10),' ')    --^^^^^^^^^^^^^^^           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^    --only add the above code to strings that are having line breaks, not to numbers or dates    FROM YourTable...    WHERE ... 

This will replace all the line breaks with a space character.

Run this to "get" all characters permitted in a char() and varchar():

;WITH AllNumbers AS (     SELECT 1 AS Number     UNION ALL     SELECT Number+1         FROM AllNumbers         WHERE Number+1<256 ) SELECT Number AS ASCII_Value,CHAR(Number) AS ASCII_Char FROM AllNumbers OPTION (MAXRECURSION 256) 

OUTPUT:

ASCII_Value ASCII_Char ----------- ---------- 1            2            3            4            5            6            7            8            9                10            11           12           13            14           15           16           17           18           19           20           21           22           23           24           25           26           27           28           29           30           31           32            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          @ 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 91          [ 92          \ 93          ] 94          ^ 95          _ 96          ` 97          a 98          b 99          c 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         ~ 127          128         € 129          130         ‚ 131         ƒ 132         „ 133         … 134         † 135         ‡ 136         ˆ 137         ‰ 138         Š 139         ‹ 140         Œ 141          142         Ž 143          144          145         ‘ 146         ’ 147         “ 148         ” 149         • 150         – 151         — 152         ˜ 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         ¿ 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         ý 254         þ 255         ÿ  (255 row(s) affected)