IsNumeric != IsNumber

According to Books Online:
ISNUMERIC determines whether an expression is a valid numeric type. However, ISNUMERIC also returns 1 for some characters that are not numbers, such as plus (+), minus (-), and valid currency symbols such as the dollar sign ($).
 
So what other characters/symbols are also treated as the numeric type? I’ve built two tables to help answer the question.
 
ASCII Symbol Description IsNumeric
9   tab 1
10   newline 1
13   carriage return 1
32   space 0
33 ! exclamation mark 0
34 double quote 0
35 # hash 0
36 $ dollar 1
37 % percent 0
38 & ampersand 0
39 single quote 0
40 ( open bracket 0
41 ) close bracket 0
42 * multiply/star 0
43 + plus 1
44 , comma 1
45 minus/dash 1
46 . full stop 1
47 / divide/forward slash 0
48 0 0 1
49 1 1 1
50 2 2 1
51 3 3 1
52 4 4 1
53 5 5 1
54 6 6 1
55 7 7 1
56 8 8 1
57 9 9 1
58 : colon 0
59 ; semicolon 0
60 < less than 0
61 = equal 0
62 > greater than 0
63 ? question mark 0
64 @ at 0
65 A A 0
66 B B 0
67 C C 0
68 D D 0
69 E E 0
70 F F 0
71 G G 0
72 H H 0
73 I I 0
74 J J 0
75 K K 0
76 L L 0
77 M M 0
78 N N 0
79 O O 0
80 P P 0
81 Q Q 0
82 R R 0
83 S S 0
84 T T 0
85 U U 0
86 V V 0
87 W W 0
88 X X 0
89 Y Y 0
90 Z Z 0
91 [ open square bracket 0
92 \ backslash 1
93 ] close square bracket 0
94 ^ power 0
95 _ underscore 0
96 ` apostrophe 0
97 a a 0
98 b b 0
99 c c 0
100 d d 0
101 e e 0
102 f f 0
103 g g 0
104 h h 0
105 i i 0
106 j j 0
107 k k 0
108 l l 0
109 m m 0
110 n n 0
111 o o 0
112 p p 0
113 q q 0
114 r r 0
115 s s 0
116 t t 0
117 u u 0
118 v v 0
119 w w 0
120 x x 0
121 y y 0
122 z z 0
123 { open brace 0
124 | vertical bar/pipe 0
125 } close brace 0
126 ~ tilde 0
ASCII Symbol Expression1 IsNumeric Expression2 IsNumeric Expression3 IsNumeric
9 (tab) 1(tab) 0 (tab)2 1 1(tab)2 0
10 (newline) 1(newline) 0 (newline)2 1 1(newline)2 0
13 (carriage return) 1(carriage return) 0 (carriage return)2 1 1(carriage return)2 0
32 (space) 1(space) 1 (space)2 1 1(space)2 0
33 ! 1! 0 !2 0 1!2 0
34 1″ 0 “2 0 1″2 0
35 # 1# 0 #2 0 1#2 0
36 $ 1$ 0 $2 1 1$2 0
37 % 1% 0 %2 0 1%2 0
38 & 1& 0 &2 0 1&2 0
39 1′ 0 ‘2 0 1’2 0
40 ( 1( 0 (2 0 1(2 0
41 ) 1) 0 )2 0 1)2 0
42 * 1* 0 *2 0 1*2 0
43 + 1+ 0 +2 1 1+2 0
44 , 1, 1 ,2 1 1,2 1
45 1- 0 -2 1 1-2 0
46 . 1. 1 .2 1 1.2 1
47 / 1/ 0 /2 0 1/2 0
48 0 10 1 02 1 102 1
49 1 11 1 12 1 112 1
50 2 12 1 22 1 122 1
51 3 13 1 32 1 132 1
52 4 14 1 42 1 142 1
53 5 15 1 52 1 152 1
54 6 16 1 62 1 162 1
55 7 17 1 72 1 172 1
56 8 18 1 82 1 182 1
57 9 19 1 92 1 192 1
58 : 1: 0 :2 0 1:2 0
59 ; 1; 0 ;2 0 1;2 0
60 < 1< 0 <2 0 1<2 0
61 = 1= 0 =2 0 1=2 0
62 > 1> 0 >2 0 1>2 0
63 ? 1? 0 ?2 0 1?2 0
64 @ 1@ 0 @2 0 1@2 0
65 A 1A 0 A2 0 1A2 0
66 B 1B 0 B2 0 1B2 0
67 C 1C 0 C2 0 1C2 0
68 D 1D 0 D2 0 1D2 1
69 E 1E 0 E2 0 1E2 1
70 F 1F 0 F2 0 1F2 0
71 G 1G 0 G2 0 1G2 0
72 H 1H 0 H2 0 1H2 0
73 I 1I 0 I2 0 1I2 0
74 J 1J 0 J2 0 1J2 0
75 K 1K 0 K2 0 1K2 0
76 L 1L 0 L2 0 1L2 0
77 M 1M 0 M2 0 1M2 0
78 N 1N 0 N2 0 1N2 0
79 O 1O 0 O2 0 1O2 0
80 P 1P 0 P2 0 1P2 0
81 Q 1Q 0 Q2 0 1Q2 0
82 R 1R 0 R2 0 1R2 0
83 S 1S 0 S2 0 1S2 0
84 T 1T 0 T2 0 1T2 0
85 U 1U 0 U2 0 1U2 0
86 V 1V 0 V2 0 1V2 0
87 W 1W 0 W2 0 1W2 0
88 X 1X 0 X2 0 1X2 0
89 Y 1Y 0 Y2 0 1Y2 0
90 Z 1Z 0 Z2 0 1Z2 0
91 [ 1[ 0 [2 0 1[2 0
92 \ 1\ 0 \2 1 1\2 0
93 ] 1] 0 ]2 0 1]2 0
94 ^ 1^ 0 ^2 0 1^2 0
95 _ 1_ 0 _2 0 1_2 0
96 ` 1` 0 `2 0 1`2 0
97 a 1a 0 a2 0 1a2 0
98 b 1b 0 b2 0 1b2 0
99 c 1c 0 c2 0 1c2 0
100 d 1d 0 d2 0 1d2 1
101 e 1e 0 e2 0 1e2 1
102 f 1f 0 f2 0 1f2 0
103 g 1g 0 g2 0 1g2 0
104 h 1h 0 h2 0 1h2 0
105 i 1i 0 i2 0 1i2 0
106 j 1j 0 j2 0 1j2 0
107 k 1k 0 k2 0 1k2 0
108 l 1l 0 l2 0 1l2 0
109 m 1m 0 m2 0 1m2 0
110 n 1n 0 n2 0 1n2 0
111 o 1o 0 o2 0 1o2 0
112 p 1p 0 p2 0 1p2 0
113 q 1q 0 q2 0 1q2 0
114 r 1r 0 r2 0 1r2 0
115 s 1s 0 s2 0 1s2 0
116 t 1t 0 t2 0 1t2 0
117 u 1u 0 u2 0 1u2 0
118 v 1v 0 v2 0 1v2 0
119 w 1w 0 w2 0 1w2 0
120 x 1x 0 x2 0 1×2 0
121 y 1y 0 y2 0 1y2 0
122 z 1z 0 z2 0 1z2 0
123 { 1{ 0 {2 0 1{2 0
124 | 1| 0 |2 0 1|2 0
125 } 1} 0 }2 0 1}2 0
126 ~ 1~ 0 ~2 0 1~2 0
 
If an expression is numeric, then it can be converted to either int, float or money. Looking at the tables above, most of the numeric expressions are pretty straight forward. There is an odd one out that troubled me for a while.
 
What is “\2”? What sort of numeric representation is this? To answer that, I tried various conversions:

select cast(‘\2’ as int)
go
select cast(‘\2’ as float)
go
select cast(‘\2’ as money)
go

and got:


Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '\2' to data type int.
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to float.

(1 row(s) affected)

So it represents a currency. I understand that $ is the symbol for dollar, but what is \? It turned out to be either the Japanese Yen or Korean Won.
 
–oo00oo–
 
For more info, please refer to the following blog post: