ISNUMERIC

[hoelder]

When you pass a '.' to the function isnumeric, it returns 1 for yes and that
throws your conversion from varchar to a number into an error.

[Thomas Coleman]

Yes, there was quite row about this in this newsgroup some time ago. The short
answer is that this is a design feature. After you stop laughing, the
explanation is that the IsNumeric function returns 1 if the value can be convert
to an Integer, Decimal, Float or Money data type. Since a period is a valid
character in floats and money data types, it returns 1.

Stupid I will grant you; which is why I avoid using IsNumeric.

Thomas

"hoelder" <hoe...@discussions.microsoft.com> wrote in message
news:15FA26CD-25E2-4FB0...@microsoft.com...

[JT]

Actually, other currency related symbols (such as $ and £) and a handful of
non-printable characters are treated as numeric by this function as well.
Stragest of all is the tab char(9) character.

set nocount on
create table #y
(
x int,
char_ char,
isnumeric_ int
)

declare @x as int
select @x = 1
while @x < 255
begin
insert into #y
select @x, char(@x), isnumeric(char(@x))
select @x = @x + 1
end
select * from #y
drop table #y

x char_ isnumeric_
----------- ----- -----------
1 0
2 0
3 0
4 0
5 0
6 0
7 0
8 0
9 1
10
1
11 1
12 1
13
1
14 0
15 0
16 0
17 0
18 0
19 0
20 0
21 0
22 0
23 0
24 0
25 0
26 0
27 0
28 0
29 0
30 0
31 0
32 0
33 ! 0
34 " 0
35 # 0
36 $ 1
37 % 0
38 & 0
39 ' 0
40 ( 0
41 ) 0
42 * 0
43 + 1
44 , 1
45 - 1
46 . 1
47 / 0
48 0 1
49 1 1
50 2 1
51 3 1
52 4 1
53 5 1
54 6 1
55 7 1
56 8 1
57 9 1
58 : 0
59 ; 0
60 < 0
61 = 0
62 > 0
63 ? 0
64 @ 0
65 A 0
66 B 0
67 C 0
68 D 0
69 E 0
70 F 0
71 G 0
72 H 0
73 I 0
74 J 0
75 K 0
76 L 0
77 M 0
78 N 0
79 O 0
80 P 0
81 Q 0
82 R 0
83 S 0
84 T 0
85 U 0
86 V 0
87 W 0
88 X 0
89 Y 0
90 Z 0
91 [ 0
92 \ 0
93 ] 0
94 ^ 0
95 _ 0
96 ` 0
97 a 0
98 b 0
99 c 0
100 d 0
101 e 0
102 f 0
103 g 0
104 h 0
105 i 0
106 j 0
107 k 0
108 l 0
109 m 0
110 n 0
111 o 0
112 p 0
113 q 0
114 r 0
115 s 0
116 t 0
117 u 0
118 v 0
119 w 0
120 x 0
121 y 0
122 z 0
123 { 0
124 | 0
125 } 0
126 ~ 0
127 0
128 ? 1
129 Å 0
130 , 0
131 f 0
132 " 0
133 . 0
134 ? 0
135 ? 0
136 ^ 0
137 ? 0
138 S 0
139 < 0
140 O 0
141 ç 0
142 Z 0
143 è 0
144 ê 0
145 ' 0
146 ' 0
147 " 0
148 " 0
149 . 0
150 - 0
151 - 0
152 ~ 0
153 T 0
154 s 0
155 > 0
156 o 0
157 ù 0
158 z 0
159 Y 0
160 1
161 ° 0
162 ¢ 0
163 £ 1
164 § 1
165 • 1
166 ¶ 0
167 ß 0
168 ® 0
169 © 0
170 ™ 0
171 ´ 0
172 ¨ 0
173 ≠ 0
174 Æ 0
175 Ø 0
176 ∞ 0
177 ± 0
178 ≤ 0
179 ≥ 0
180 ¥ 0
181 µ 0
182 ∂ 0
183 ∑ 0
184 ∏ 0
185 π 0
186 ∫ 0
187 ª 0
188 º 0
189 Ω 0
190 æ 0
191 ø 0
192 ¿ 0
193 ¡ 0
194 ¬ 0
195 √ 0
196 ƒ 0
197 ≈ 0
198 ∆ 0
199 « 0
200 » 0
201 … 0
202   0
203 À 0
204 Ã 0
205 Õ 0
206 Π0
207 œ 0
208 – 0
209 — 0
210 “ 0
211 ” 0
212 ‘ 0
213 ’ 0
214 ÷ 0
215 ◊ 0
216 ÿ 0
217 Ÿ 0
218 ⁄ 0
219 € 0
220 ‹ 0
221 › 0
222 fi 0
223 fl 0
224 ‡ 0
225 · 0
226 ‚ 0
227 „ 0
228 ‰ 0
229 Â 0
230 Ê 0
231 Á 0
232 Ë 0
233 È 0
234 Í 0
235 Î 0
236 Ï 0
237 Ì 0
238 Ó 0
239 Ô 0
240  0
241 Ò 0
242 Ú 0
243 Û 0
244 Ù 0
245 ı 0
246 ˆ 0
247 ˜ 0
248 ¯ 0
249 ˘ 0
250 ˙ 0
251 ˚ 0
252 ¸ 0
253 ˝ 0
254 ˛ 0

"hoelder" <hoe...@discussions.microsoft.com> wrote in message
news:15FA26CD-25E2-4FB0...@microsoft.com...

[Raymond D'Anjou]

Thomas has already suggested that you avoid using isNumeric.
There are also other bizare values that return 1, like '1e12'
Here's a UDF that I picked up somewhere (my apologies to the author).
CREATE FUNCTION dbo.IsReallyNumeric(@num VARCHAR(19))
RETURNS BIT
BEGIN
RETURN
CASE WHEN LEFT(@num,1) LIKE '[-0-9+.]'
AND PATINDEX('%[^0-9.]%', SUBSTRING(@num, 2, 18)) = 0
AND LEN(@num) - LEN(REPLACE(@num, '.', '')) <=1
THEN 1
ELSE 0 END
END

"hoelder" <hoe...@discussions.microsoft.com> wrote in message
news:15FA26CD-25E2-4FB0...@microsoft.com...

[Aaron Bertrand [SQL Server MVP]]

> Here's a UDF that I picked up somewhere (my apologies to the author).

No worries.
http://www.aspfaq.com/2390

[Razvan Socol]

Here are my own versions for these functions:

CREATE FUNCTION dbo.IsSomethingInteger(@num VARCHAR(64))

RETURNS BIT
BEGIN
RETURN CASE WHEN

(LEFT(@num,1) LIKE '[0-9]' OR LEFT(@num,1)='-' AND LEN(@num)>1)
AND PATINDEX('%[^0-9]%', SUBSTRING(@num, 2, 64)) = 0

THEN 1 ELSE 0 END
END

CREATE FUNCTION dbo.IsSomethingNumeric(@num VARCHAR(64))

RETURNS BIT
BEGIN
RETURN CASE WHEN

LEN(@num)>0
AND @num NOT LIKE '%[^0-9.-]%'
AND (
@num NOT LIKE '%.%'
OR LEN(@num)-LEN(REPLACE(@num,'.',''))=1
AND @num LIKE '%[0-9]%'
) AND (
@num NOT LIKE '%-%'
OR LEN(@num)-LEN(REPLACE(@num,'-',''))=1
AND LEFT(@num,1)='-'
AND LEN(@num)>1
)

THEN 1 ELSE 0 END
END

Compared to Aaron's functions, I think my functions are slightly better
because they are shorter and do not use variables, because all the work
is done in a single statement (so it's easier to rewrite them in the
WHERE clause, without a UDF).

Razvan

[Michael C#]

Actually 1e12 is understandable, since that's standard notation (1e+12 = 1 *
10 ^ 12). What's really weird is the "1d12" notation that passes the test.

"Raymond D'Anjou" <rda...@savantsoftNOSPAM.net> wrote in message
news:%23VwboGl...@tk2msftngp13.phx.gbl...