How can I see all the special characters permissible in a varchar or char field in SQL Server

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  quot non-printable quot  characters  How to print them  as they are not printable but perform some action on the text

User · Answer

i think that special characters are   and   only      query will list both   DECLARE   str VARCHAR 50   SET  str     azAB09ram reddy wer45    CHAR 5     a b    SELECT DISTINCT poschar  FROM   MASTER  spt values S         CROSS APPLY  SELECT SUBSTRING  str NUMBER 1  AS poschar  t  WHERE  NUMBER  gt  0         AND NUMBER  lt   LEN  str          AND NOT  ASCII t poschar  BETWEEN 65 AND 90                   OR ASCII t poschar  BETWEEN 97 AND 122                   OR ASCII t poschar  BETWEEN 48 AND 57

User · Answer

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 lt 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           amp  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           lt  61            62           gt  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

User · Answer

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

User · Answer

The specific characters that can be stored in a varchar or char column depend upon the column collation  See my answer here for a script that will show you these for the various different collations   If you want to find all characters outside a particular ASCII range see my answer here

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

Examples related to sql

Examples related to sql-server