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.

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


Show quoteHide quote

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         Þ     0
223         ß     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

Show quoteHide quote

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

Show quoteHide quote

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

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

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.

Show quoteHide quote

Deadlock when not in transaction?
32734 limit on records... from accessing data with a cursor....
SQL statement puzzle
How many NDF files do we need?!?
Query Sorting Question