ISNUMERIC() bug?

Question

Post reply

ISNUMERIC() bug?

DK Young

SSC Veteran

Points: 238
More actions
December 12, 2005 at 12:24 pm

#90093

Hello, I just encountered a strange result using the ISNUMERIC function...specifically something like SELECT ISNUMERIC('9990D7') or SELECT ISNUMERIC('9D7'). These statements return a 1, meaning it evaluated the value as a numeric value. I noticed this is true for values that start with one or more digits, followed by either a 'D' or an 'E' and followed by one or more digits.
Can anyone reproduce this? Or has anyone else encountered this?
Thanks!
Dan

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply

David.Poole SSC Guru Points: 76001 More actions · Answer 1

I seem to remember that there was a thread about it once before but can't find it now.

The E is something to do with powers of 10. 1E1 = 10, 1E2 = 100 etc hence the ISNUMERIC thing.

There was a logical reason for the D as well but I can't remember what it was.

Ultimately it is not a bug just the single most annoying "feature" I have ever come across.

LinkedIn Profile

DK Young SSC Veteran Points: 238 More actions · Answer 2

Thanks for the reply and the explanation. The E makes sense, though it would have been nice to have read that in the docs. It's actually the D that's killing me. Oh well, I guess I can scan through each position and check for valid numeric that way. Thanks again for the fast response!

Dan

David.Poole SSC Guru Points: 76001 More actions · Answer 3

Have you considered using LIKE %[^0-9]% as a comparison?

In otherwords if your string contains characters in that are not digits?

LinkedIn Profile

Mark Johnson-176476 SSCommitted Points: 1603 More actions · Answer 4

From Frank Kalis' post on July 20:

"D and E are used in Fortran to represent FLOATs in scientific notation. SQL Server internally uses C routines for ISNUMERIC that follow these formats. So ISNUMERIC returns 1 for 0d830 and 0e830, because they are evaluated to be convertible to FLOAT.

The reason why

select convert(float,'0D830')

returns 0 is simple. 0d830 is a shortcut to 0*10^830 which obviously is 0 for every INT you place after the d. "

Vladan SSC-Insane Points: 22072 More actions · Answer 5

ISNUMERIC is not a good way to find out, whether a string contains only numbers - it is rather a test, whether conversion to number will cause error.

SELECT ISNUMERIC('+002') --> 1

SELECT ISNUMERIC('-3E265') --> 1

SELECT ISNUMERIC('6D78') --> 1

SELECT ISNUMERIC('6D7897') --> 0

BTW, not only ISNUMERIC, but also ISNULL can sometimes cause unexpected results thanks to conversion of result datatype. Try this (I have copied it about 1 year ago from one post here, I think it was by Frank Kalis, too.)

SELECT

7 / ISNULL(CAST(NULL AS int), 2.00),

7 / COALESCE(CAST(NULL AS int), 2.00),

7 / CASE WHEN CAST(NULL AS int) IS NULL THEN 2.00 END

CREATE TABLE #t(c1 CHAR(2))

INSERT INTO #t VALUES (NULL)

--ISNULL provádí konverzi a rídí se puvodní délkou sloupce

SELECT ISNULL(c1,'Frank'), COALESCE(c1,'Frank')

FROM #t

SELECT ISNULL(c1,'Frank'), COALESCE(c1,'Frank'), CASE WHEN c1 IS NULL THEN 'Frank' ELSE c1 END

FROM #t

DROP TABLE #t

DK Young SSC Veteran Points: 238 More actions · Answer 6

Good point! Thanks again for the reply. It is greatly appreciated!

DK Young SSC Veteran Points: 238 More actions · Answer 7

DK Young

SSC Veteran

Points: 238

December 14, 2005 at 7:38 pm

#609632

Okay, that explains a lot! Thanks!

sushila SSC-Dedicated Points: 35293 More actions · Answer 8

same starcast - different movie - this the thread you were looking for...?!?!

**ASCII stupid question, get a stupid ANSI !!!**

detzu SSC Enthusiast Points: 117 More actions · Answer 9

Please explain this one :

12/16/2005 2:49:21 PM: Ascii code : 0 isnumeric : 0

12/16/2005 2:49:21 PM: Ascii code : 1 isnumeric : 0

12/16/2005 2:49:21 PM: Ascii code : 2 isnumeric : 0

12/16/2005 2:49:21 PM: Ascii code : 3 isnumeric : 0

12/16/2005 2:49:21 PM: Ascii code : 4 isnumeric : 0

12/16/2005 2:49:21 PM: Ascii code : 5 isnumeric : 0

12/16/2005 2:49:21 PM: Ascii code : 6 isnumeric : 0

12/16/2005 2:49:21 PM: Ascii code : 7 isnumeric : 0

12/16/2005 2:49:21 PM: Ascii code : 8 isnumeric : 0

12/16/2005 2:49:21 PM: Ascii code : 9 isnumeric : 1

12/16/2005 2:49:21 PM: Ascii code : 10 isnumeric : 1

12/16/2005 2:49:21 PM: Ascii code : 11 isnumeric : 1

12/16/2005 2:49:21 PM: Ascii code : 12 isnumeric : 1

12/16/2005 2:49:21 PM: Ascii code : 13 isnumeric : 1

12/16/2005 2:49:21 PM: Ascii code : 14 isnumeric : 0

12/16/2005 2:49:21 PM: Ascii code : 15 isnumeric : 0

12/16/2005 2:49:21 PM: Ascii code : 16 isnumeric : 0

12/16/2005 2:49:21 PM: Ascii code : 17 isnumeric : 0

12/16/2005 2:49:21 PM: Ascii code : 18 isnumeric : 0

12/16/2005 2:49:21 PM: Ascii code : 19 isnumeric : 0

12/16/2005 2:49:22 PM: Ascii code : 20 isnumeric : 0

12/16/2005 2:49:22 PM: Ascii code : 21 isnumeric : 0

12/16/2005 2:49:22 PM: Ascii code : 22 isnumeric : 0

12/16/2005 2:49:22 PM: Ascii code : 23 isnumeric : 0

12/16/2005 2:49:22 PM: Ascii code : 24 isnumeric : 0

12/16/2005 2:49:22 PM: Ascii code : 25 isnumeric : 0

12/16/2005 2:49:22 PM: Ascii code : 26 isnumeric : 0

12/16/2005 2:49:22 PM: Ascii code : 27 isnumeric : 0

12/16/2005 2:49:22 PM: Ascii code : 28 isnumeric : 0

12/16/2005 2:49:22 PM: Ascii code : 29 isnumeric : 0

12/16/2005 2:49:22 PM: Ascii code : 30 isnumeric : 0

12/16/2005 2:49:22 PM: Ascii code : 31 isnumeric : 0

12/16/2005 2:49:22 PM: Ascii code : 32 isnumeric : 0

12/16/2005 2:49:22 PM: Ascii code : 33 isnumeric : 0

12/16/2005 2:49:22 PM: Ascii code : 34 isnumeric : 0

12/16/2005 2:49:22 PM: Ascii code : 35 isnumeric : 0

12/16/2005 2:49:22 PM: Ascii code : 36 isnumeric : 1

12/16/2005 2:49:22 PM: Ascii code : 37 isnumeric : 0

12/16/2005 2:49:22 PM: Ascii code : 38 isnumeric : 0

12/16/2005 2:49:22 PM: Ascii code : 39 isnumeric : 0

12/16/2005 2:49:22 PM: Ascii code : 40 isnumeric : 0

12/16/2005 2:49:22 PM: Ascii code : 41 isnumeric : 0

12/16/2005 2:49:22 PM: Ascii code : 42 isnumeric : 0

12/16/2005 2:49:22 PM: Ascii code : 43 isnumeric : 1

12/16/2005 2:49:22 PM: Ascii code : 44 isnumeric : 1

12/16/2005 2:49:22 PM: Ascii code : 45 isnumeric : 1

12/16/2005 2:49:22 PM: Ascii code : 46 isnumeric : 1

12/16/2005 2:49:22 PM: Ascii code : 47 isnumeric : 0

12/16/2005 2:49:22 PM: Ascii code : 48 isnumeric : 1

12/16/2005 2:49:22 PM: Ascii code : 49 isnumeric : 1

12/16/2005 2:49:22 PM: Ascii code : 50 isnumeric : 1

12/16/2005 2:49:22 PM: Ascii code : 51 isnumeric : 1

12/16/2005 2:49:22 PM: Ascii code : 52 isnumeric : 1

12/16/2005 2:49:22 PM: Ascii code : 53 isnumeric : 1

12/16/2005 2:49:22 PM: Ascii code : 54 isnumeric : 1

12/16/2005 2:49:22 PM: Ascii code : 55 isnumeric : 1

12/16/2005 2:49:22 PM: Ascii code : 56 isnumeric : 1

12/16/2005 2:49:22 PM: Ascii code : 57 isnumeric : 1

12/16/2005 2:49:22 PM: Ascii code : 58 isnumeric : 0

12/16/2005 2:49:22 PM: Ascii code : 59 isnumeric : 0

12/16/2005 2:49:22 PM: Ascii code : 60 isnumeric : 0

12/16/2005 2:49:22 PM: Ascii code : 61 isnumeric : 0

12/16/2005 2:49:23 PM: Ascii code : 62 isnumeric : 0

12/16/2005 2:49:23 PM: Ascii code : 63 isnumeric : 0

12/16/2005 2:49:23 PM: Ascii code : 64 isnumeric : 0

12/16/2005 2:49:23 PM: Ascii code : 65 isnumeric : 0

12/16/2005 2:49:23 PM: Ascii code : 66 isnumeric : 0

12/16/2005 2:49:23 PM: Ascii code : 67 isnumeric : 0

12/16/2005 2:49:23 PM: Ascii code : 68 isnumeric : 0

12/16/2005 2:49:23 PM: Ascii code : 69 isnumeric : 0

12/16/2005 2:49:23 PM: Ascii code : 70 isnumeric : 0

12/16/2005 2:49:23 PM: Ascii code : 71 isnumeric : 0

12/16/2005 2:49:23 PM: Ascii code : 72 isnumeric : 0

12/16/2005 2:49:23 PM: Ascii code : 73 isnumeric : 0

12/16/2005 2:49:23 PM: Ascii code : 74 isnumeric : 0

12/16/2005 2:49:23 PM: Ascii code : 75 isnumeric : 0

12/16/2005 2:49:23 PM: Ascii code : 76 isnumeric : 0

12/16/2005 2:49:23 PM: Ascii code : 77 isnumeric : 0

12/16/2005 2:49:23 PM: Ascii code : 78 isnumeric : 0

12/16/2005 2:49:23 PM: Ascii code : 79 isnumeric : 0

12/16/2005 2:49:23 PM: Ascii code : 80 isnumeric : 0

12/16/2005 2:49:23 PM: Ascii code : 81 isnumeric : 0

12/16/2005 2:49:23 PM: Ascii code : 82 isnumeric : 0

12/16/2005 2:49:23 PM: Ascii code : 83 isnumeric : 0

12/16/2005 2:49:23 PM: Ascii code : 84 isnumeric : 0

12/16/2005 2:49:23 PM: Ascii code : 85 isnumeric : 0

12/16/2005 2:49:23 PM: Ascii code : 86 isnumeric : 0

12/16/2005 2:49:23 PM: Ascii code : 87 isnumeric : 0

12/16/2005 2:49:23 PM: Ascii code : 88 isnumeric : 0

12/16/2005 2:49:23 PM: Ascii code : 89 isnumeric : 0

12/16/2005 2:49:23 PM: Ascii code : 90 isnumeric : 0

12/16/2005 2:49:23 PM: Ascii code : 91 isnumeric : 0

12/16/2005 2:49:23 PM: Ascii code : 92 isnumeric : 0

12/16/2005 2:49:23 PM: Ascii code : 93 isnumeric : 0

12/16/2005 2:49:23 PM: Ascii code : 94 isnumeric : 0

12/16/2005 2:49:23 PM: Ascii code : 95 isnumeric : 0

12/16/2005 2:49:23 PM: Ascii code : 96 isnumeric : 0

12/16/2005 2:49:23 PM: Ascii code : 97 isnumeric : 0

12/16/2005 2:49:23 PM: Ascii code : 98 isnumeric : 0

12/16/2005 2:49:23 PM: Ascii code : 99 isnumeric : 0

12/16/2005 2:49:23 PM: Ascii code : 100 isnumeric : 0

12/16/2005 2:49:23 PM: Ascii code : 101 isnumeric : 0

12/16/2005 2:49:23 PM: Ascii code : 102 isnumeric : 0

12/16/2005 2:49:23 PM: Ascii code : 103 isnumeric : 0

12/16/2005 2:49:24 PM: Ascii code : 104 isnumeric : 0

12/16/2005 2:49:24 PM: Ascii code : 105 isnumeric : 0

12/16/2005 2:49:24 PM: Ascii code : 106 isnumeric : 0

12/16/2005 2:49:24 PM: Ascii code : 107 isnumeric : 0

12/16/2005 2:49:24 PM: Ascii code : 108 isnumeric : 0

12/16/2005 2:49:24 PM: Ascii code : 109 isnumeric : 0

12/16/2005 2:49:24 PM: Ascii code : 110 isnumeric : 0

12/16/2005 2:49:24 PM: Ascii code : 111 isnumeric : 0

12/16/2005 2:49:24 PM: Ascii code : 112 isnumeric : 0

12/16/2005 2:49:24 PM: Ascii code : 113 isnumeric : 0

12/16/2005 2:49:24 PM: Ascii code : 114 isnumeric : 0

12/16/2005 2:49:24 PM: Ascii code : 115 isnumeric : 0

12/16/2005 2:49:24 PM: Ascii code : 116 isnumeric : 0

12/16/2005 2:49:24 PM: Ascii code : 117 isnumeric : 0

12/16/2005 2:49:24 PM: Ascii code : 118 isnumeric : 0

12/16/2005 2:49:24 PM: Ascii code : 119 isnumeric : 0

12/16/2005 2:49:24 PM: Ascii code : 120 isnumeric : 0

12/16/2005 2:49:24 PM: Ascii code : 121 isnumeric : 0

12/16/2005 2:49:24 PM: Ascii code : 122 isnumeric : 0

12/16/2005 2:49:24 PM: Ascii code : 123 isnumeric : 0

12/16/2005 2:49:24 PM: Ascii code : 124 isnumeric : 0

12/16/2005 2:49:24 PM: Ascii code : 125 isnumeric : 0

12/16/2005 2:49:24 PM: Ascii code : 126 isnumeric : 0

12/16/2005 2:49:24 PM: Ascii code : 127 isnumeric : 0

12/16/2005 2:49:24 PM: Ascii code : 128 isnumeric : 1

12/16/2005 2:49:24 PM: Ascii code : 129 isnumeric : 0

12/16/2005 2:49:24 PM: Ascii code : 130 isnumeric : 0

12/16/2005 2:49:24 PM: Ascii code : 131 isnumeric : 0

12/16/2005 2:49:24 PM: Ascii code : 132 isnumeric : 0

12/16/2005 2:49:24 PM: Ascii code : 133 isnumeric : 0

12/16/2005 2:49:24 PM: Ascii code : 134 isnumeric : 0

12/16/2005 2:49:24 PM: Ascii code : 135 isnumeric : 0

12/16/2005 2:49:24 PM: Ascii code : 136 isnumeric : 0

12/16/2005 2:49:24 PM: Ascii code : 137 isnumeric : 0

12/16/2005 2:49:24 PM: Ascii code : 138 isnumeric : 0

12/16/2005 2:49:24 PM: Ascii code : 139 isnumeric : 0

12/16/2005 2:49:24 PM: Ascii code : 140 isnumeric : 0

12/16/2005 2:49:24 PM: Ascii code : 141 isnumeric : 0

12/16/2005 2:49:24 PM: Ascii code : 142 isnumeric : 0

12/16/2005 2:49:24 PM: Ascii code : 143 isnumeric : 0

12/16/2005 2:49:24 PM: Ascii code : 144 isnumeric : 0

12/16/2005 2:49:24 PM: Ascii code : 145 isnumeric : 0

12/16/2005 2:49:25 PM: Ascii code : 146 isnumeric : 0

12/16/2005 2:49:25 PM: Ascii code : 147 isnumeric : 0

12/16/2005 2:49:25 PM: Ascii code : 148 isnumeric : 0

12/16/2005 2:49:25 PM: Ascii code : 149 isnumeric : 0

12/16/2005 2:49:25 PM: Ascii code : 150 isnumeric : 0

12/16/2005 2:49:25 PM: Ascii code : 151 isnumeric : 0

12/16/2005 2:49:25 PM: Ascii code : 152 isnumeric : 0

12/16/2005 2:49:25 PM: Ascii code : 153 isnumeric : 0

12/16/2005 2:49:25 PM: Ascii code : 154 isnumeric : 0

12/16/2005 2:49:25 PM: Ascii code : 155 isnumeric : 0

12/16/2005 2:49:25 PM: Ascii code : 156 isnumeric : 0

12/16/2005 2:49:25 PM: Ascii code : 157 isnumeric : 0

12/16/2005 2:49:25 PM: Ascii code : 158 isnumeric : 0

12/16/2005 2:49:25 PM: Ascii code : 159 isnumeric : 0

12/16/2005 2:49:25 PM: Ascii code : 160 isnumeric : 1

12/16/2005 2:49:25 PM: Ascii code : 161 isnumeric : 0

12/16/2005 2:49:25 PM: Ascii code : 162 isnumeric : 0

12/16/2005 2:49:25 PM: Ascii code : 163 isnumeric : 1

12/16/2005 2:49:25 PM: Ascii code : 164 isnumeric : 1

12/16/2005 2:49:25 PM: Ascii code : 165 isnumeric : 1

12/16/2005 2:49:25 PM: Ascii code : 166 isnumeric : 0

12/16/2005 2:49:25 PM: Ascii code : 167 isnumeric : 0

12/16/2005 2:49:25 PM: Ascii code : 168 isnumeric : 0

12/16/2005 2:49:25 PM: Ascii code : 169 isnumeric : 0

12/16/2005 2:49:25 PM: Ascii code : 170 isnumeric : 0

12/16/2005 2:49:25 PM: Ascii code : 171 isnumeric : 0

12/16/2005 2:49:25 PM: Ascii code : 172 isnumeric : 0

12/16/2005 2:49:25 PM: Ascii code : 173 isnumeric : 0

12/16/2005 2:49:25 PM: Ascii code : 174 isnumeric : 0

12/16/2005 2:49:25 PM: Ascii code : 175 isnumeric : 0

12/16/2005 2:49:25 PM: Ascii code : 176 isnumeric : 0

12/16/2005 2:49:25 PM: Ascii code : 177 isnumeric : 0

12/16/2005 2:49:25 PM: Ascii code : 178 isnumeric : 0

12/16/2005 2:49:25 PM: Ascii code : 179 isnumeric : 0

12/16/2005 2:49:25 PM: Ascii code : 180 isnumeric : 0

12/16/2005 2:49:25 PM: Ascii code : 181 isnumeric : 0

12/16/2005 2:49:25 PM: Ascii code : 182 isnumeric : 0

12/16/2005 2:49:25 PM: Ascii code : 183 isnumeric : 0

12/16/2005 2:49:25 PM: Ascii code : 184 isnumeric : 0

12/16/2005 2:49:25 PM: Ascii code : 185 isnumeric : 0

12/16/2005 2:49:25 PM: Ascii code : 186 isnumeric : 0

12/16/2005 2:49:26 PM: Ascii code : 187 isnumeric : 0

12/16/2005 2:49:26 PM: Ascii code : 188 isnumeric : 0

12/16/2005 2:49:26 PM: Ascii code : 189 isnumeric : 0

12/16/2005 2:49:26 PM: Ascii code : 190 isnumeric : 0

12/16/2005 2:49:26 PM: Ascii code : 191 isnumeric : 0

12/16/2005 2:49:26 PM: Ascii code : 192 isnumeric : 0

12/16/2005 2:49:26 PM: Ascii code : 193 isnumeric : 0

12/16/2005 2:49:26 PM: Ascii code : 194 isnumeric : 0

12/16/2005 2:49:26 PM: Ascii code : 195 isnumeric : 0

12/16/2005 2:49:26 PM: Ascii code : 196 isnumeric : 0

12/16/2005 2:49:26 PM: Ascii code : 197 isnumeric : 0

12/16/2005 2:49:26 PM: Ascii code : 198 isnumeric : 0

12/16/2005 2:49:26 PM: Ascii code : 199 isnumeric : 0

12/16/2005 2:49:26 PM: Ascii code : 200 isnumeric : 0

12/16/2005 2:49:26 PM: Ascii code : 201 isnumeric : 0

12/16/2005 2:49:26 PM: Ascii code : 202 isnumeric : 0

12/16/2005 2:49:26 PM: Ascii code : 203 isnumeric : 0

12/16/2005 2:49:26 PM: Ascii code : 204 isnumeric : 0

12/16/2005 2:49:26 PM: Ascii code : 205 isnumeric : 0

12/16/2005 2:49:26 PM: Ascii code : 206 isnumeric : 0

12/16/2005 2:49:26 PM: Ascii code : 207 isnumeric : 0

12/16/2005 2:49:26 PM: Ascii code : 208 isnumeric : 0

12/16/2005 2:49:26 PM: Ascii code : 209 isnumeric : 0

12/16/2005 2:49:26 PM: Ascii code : 210 isnumeric : 0

12/16/2005 2:49:26 PM: Ascii code : 211 isnumeric : 0

12/16/2005 2:49:26 PM: Ascii code : 212 isnumeric : 0

12/16/2005 2:49:26 PM: Ascii code : 213 isnumeric : 0

12/16/2005 2:49:26 PM: Ascii code : 214 isnumeric : 0

12/16/2005 2:49:26 PM: Ascii code : 215 isnumeric : 0

12/16/2005 2:49:26 PM: Ascii code : 216 isnumeric : 0

12/16/2005 2:49:26 PM: Ascii code : 217 isnumeric : 0

12/16/2005 2:49:26 PM: Ascii code : 218 isnumeric : 0

12/16/2005 2:49:26 PM: Ascii code : 219 isnumeric : 0

12/16/2005 2:49:26 PM: Ascii code : 220 isnumeric : 0

12/16/2005 2:49:26 PM: Ascii code : 221 isnumeric : 0

12/16/2005 2:49:26 PM: Ascii code : 222 isnumeric : 0

12/16/2005 2:49:26 PM: Ascii code : 223 isnumeric : 0

12/16/2005 2:49:26 PM: Ascii code : 224 isnumeric : 0

12/16/2005 2:49:26 PM: Ascii code : 225 isnumeric : 0

12/16/2005 2:49:26 PM: Ascii code : 226 isnumeric : 0

12/16/2005 2:49:26 PM: Ascii code : 227 isnumeric : 0

12/16/2005 2:49:27 PM: Ascii code : 228 isnumeric : 0

12/16/2005 2:49:27 PM: Ascii code : 229 isnumeric : 0

12/16/2005 2:49:27 PM: Ascii code : 230 isnumeric : 0

12/16/2005 2:49:27 PM: Ascii code : 231 isnumeric : 0

12/16/2005 2:49:27 PM: Ascii code : 232 isnumeric : 0

12/16/2005 2:49:27 PM: Ascii code : 233 isnumeric : 0

12/16/2005 2:49:27 PM: Ascii code : 234 isnumeric : 0

12/16/2005 2:49:27 PM: Ascii code : 235 isnumeric : 0

12/16/2005 2:49:27 PM: Ascii code : 236 isnumeric : 0

12/16/2005 2:49:27 PM: Ascii code : 237 isnumeric : 0

12/16/2005 2:49:27 PM: Ascii code : 238 isnumeric : 0

12/16/2005 2:49:27 PM: Ascii code : 239 isnumeric : 0

12/16/2005 2:49:27 PM: Ascii code : 240 isnumeric : 0

12/16/2005 2:49:27 PM: Ascii code : 241 isnumeric : 0

12/16/2005 2:49:27 PM: Ascii code : 242 isnumeric : 0

12/16/2005 2:49:27 PM: Ascii code : 243 isnumeric : 0

12/16/2005 2:49:27 PM: Ascii code : 244 isnumeric : 0

12/16/2005 2:49:27 PM: Ascii code : 245 isnumeric : 0

12/16/2005 2:49:27 PM: Ascii code : 246 isnumeric : 0

12/16/2005 2:49:27 PM: Ascii code : 247 isnumeric : 0

12/16/2005 2:49:27 PM: Ascii code : 248 isnumeric : 0

12/16/2005 2:49:27 PM: Ascii code : 249 isnumeric : 0

12/16/2005 2:49:27 PM: Ascii code : 250 isnumeric : 0

12/16/2005 2:49:27 PM: Ascii code : 251 isnumeric : 0

12/16/2005 2:49:27 PM: Ascii code : 252 isnumeric : 0

12/16/2005 2:49:27 PM: Ascii code : 253 isnumeric : 0

12/16/2005 2:49:27 PM: Ascii code : 254 isnumeric : 0

12/16/2005 2:49:27 PM: Ascii code : 255 isnumeric : 0

the script to make this output :

declare @i integer

set @i = 0

while @i < 256

begin

print N'Ascii code : ' + CAST ( @i as varchar) + ' isnumeric : ' + cast (isnumeric(char(@i)) as varchar)

set @i = @i + 1

end

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 10

Sure, one complete explanation coming right up... but I'm a bit surprised you didn't figure it out yourself... remember, you asked the question...

First, I modified your code so we could see the actual character being represented instead of just the ASCII numeric representation....

declare @i integer

set @i = 0

while @i < 256

begin

print N'Ascii code : ' + CAST ( @i as varchar) +' '+ CHAR(@I)

+ ' isnumeric : ' + cast (isnumeric(char(@i)) as varchar)

set @i = @i + 1

end

Here's the result set which has been abbreviated as a readability-courtesy to show only those single characters that are considered to be numeric...

Ascii code : 9 isnumeric : 1

Ascii code : 10

isnumeric : 1

Ascii code : 11 isnumeric : 1

Ascii code : 12 isnumeric : 1

Ascii code : 13

isnumeric : 1

Ascii code : 36 $ isnumeric : 1

Ascii code : 43 + isnumeric : 1

Ascii code : 44 , isnumeric : 1

Ascii code : 45 - isnumeric : 1

Ascii code : 46 . isnumeric : 1

Ascii code : 48 0 isnumeric : 1

Ascii code : 49 1 isnumeric : 1

Ascii code : 50 2 isnumeric : 1

Ascii code : 51 3 isnumeric : 1

Ascii code : 52 4 isnumeric : 1

Ascii code : 53 5 isnumeric : 1

Ascii code : 54 6 isnumeric : 1

Ascii code : 55 7 isnumeric : 1

Ascii code : 56 8 isnumeric : 1

Ascii code : 57 9 isnumeric : 1

Ascii code : 128 € isnumeric : 1

Ascii code : 160 isnumeric : 1

Ascii code : 163 £ isnumeric : 1

Ascii code : 164 ¤ isnumeric : 1

Ascii code : 165 ¥ isnumeric : 1

Ascii 9 is a TAB character and is included because a column of numbers is frequently delimited by a TAB.

Ascii 10 is a Line Feed character and is included because the last column of numbers is frequently terminated by a Line Feed character.

Ascii 11 is a Vertical Tab character and is included because the last column of numbers is frequently terminated by a Vertical Tab character.

Ascii 12 is a Form Feed character and is included because the last column numbers of the last row is sometimes terminated by a Form Feed character.

Ascii 13 is a Carriage Return character and is included because the last column of numbers is frequently terminated by a Carriage Return character.

Ascii 36 (Dollar sign), 128 (Euro sign), 163 (British Pound sign), and 164 (Yen sign) are included because they are frequently used as enumerators to identify the type of number or, in this case, the currency type the number is meant to represent.

Ascii 43 (Plus sign), 44 (Comma), 45 (Minus sign), and 46 (Decimal place) are included because they are frequently included in numeric columns to mark where on the number line the number appears and for simple formatting.

Ascii 160 is a special "hard space" and is included because it is frequently used to left pad numeric columns so the column of numbers appears to be right justified. Ascii 32 is a "soft space" and is not included because a single space does not usually represent a column of numbers. Ascii 32 is, however, a valid numeric character when used to create right justified numbers as is Ascii 160 but a single Ascii 32 character is NOT numeric. In fact, a string of Ascii 32 spaces is not considered to be numeric but a string of spaces with even a single digit in it is considered to be numeric.

Ascii 164 is a special character and is included because it is frequently used by accountants and some software to indicate a total or subtotal of some type. It is also used by some to indicate they don't know what the enumerator is.

Ascii 48-59 are included because they represent the digits 0 through 9.

Do notice that the "e" , "d" and (everybody forgot about this) "x" are not included as numeric in the results because a single "e", "d", or "x" is NOT considered to be numeric. Two of these letters are for two different forms of scientific notation and the "x" indicates a hexidecimal output in SQL. Further, when properly formatted, the letters "a-f" are also considered to be numeric and are also not included in the list of single characters. All of these "unlisted" characters are highly positional in nature. That is, they must be in the correct position in association with numeric digits and other numeric symbology to be considered to be numeric. Depending on where they appear in a column of numbers determines if and when they will be treated as numeric or not.

ISNUMERIC is NOT and should never be treated as ISALLDIGITS. It was never intended to mean that. Use the NOT LIKE '%[^0-9]%' regular expression to determine if something ISALLDIGITS... If you try to use ISNUMERIC for that, then you're using it the wrong way.

Any more questions on ISNUMERIC?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

detzu SSC Enthusiast Points: 117 More actions · Answer 11

Thank you for the work ! It's really helpfull .

Why can not select cast ( '123'+char(160) as int) work ?

Seems to be a difference of coding between this 2 functions. ISNUMERIC working differenty as cast is not a good idea.

Again thank you for the detailed explanation.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 12

You might want to check out my reply here: http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=202581 CHAR(160) is a non-breakable space and as such considered a "noise" character in SQL Server. At least CAST treats it this way. ISNUMERIC however treats this character as thousands separator.

ISNUMERIC is probably one of the most useless functions in SQL Server, IMHO. ....at least, when you don't need to rely on this weired behaviour.

Jeff, can you give an example where 'a-f' are considered by ISNUMERIC to be convertable to a number?

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Vladan SSC-Insane Points: 22072 More actions · Answer 13

Interesting thing though... when you CAST/CONVERT to INT, CHAR(160) will raise an error. But if you try CAST/CONVERT to FLOAT, statement will succeed and the nbsp character is ignored, if it is at the beginning of string (not if it is on the right end of string).

select cast(char(160)+'1234' as float)

select convert(float, char(160)+'1234')

Probably, as Jeff said, it is ignored if used to pad the numbers from left. Well... At the same time, select ISNUMERIC(char(160) + '1234') returns 0. As mentioned already several times, ISNUMERIC is rather unpredictable and should be avoided or used with great care : some strings that return ISNUMERIC = 0 can be successfully converted to float.

Don Bryan-238042 Valued Member Points: 67 More actions · Answer 14

modify the script to examine the "numeric" characters

declare
set
while
begin



end

result

Ascii code : 9 isnumeric : 1 Char :
Ascii code : 10 isnumeric : 1 Char :
Ascii code : 11 isnumeric : 1 Char :
Ascii code : 12 isnumeric : 1 Char :
Ascii code : 13 isnumeric : 1 Char :
Ascii code : 36 isnumeric : 1 Char : $
Ascii code : 43 isnumeric : 1 Char : +
Ascii code : 44 isnumeric : 1 Char : ,
Ascii code : 45 isnumeric : 1 Char : -
Ascii code : 46 isnumeric : 1 Char : .
Ascii code : 48 isnumeric : 1 Char : 0
Ascii code : 49 isnumeric : 1 Char : 1
Ascii code : 50 isnumeric : 1 Char : 2
Ascii code : 51 isnumeric : 1 Char : 3
Ascii code : 52 isnumeric : 1 Char : 4
Ascii code : 53 isnumeric : 1 Char : 5
Ascii code : 54 isnumeric : 1 Char : 6
Ascii code : 55 isnumeric : 1 Char : 7
Ascii code : 56 isnumeric : 1 Char : 8
Ascii code : 57 isnumeric : 1 Char : 9
Ascii code : 92 isnumeric : 1 Char : \
Ascii code : 128 isnumeric : 1 Char : €
Ascii code : 160 isnumeric : 1 Char :
Ascii code : 162 isnumeric : 1 Char : ¢
Ascii code : 163 isnumeric : 1 Char : £
Ascii code : 164 isnumeric : 1 Char : ¤
Ascii code : 165 isnumeric : 1 Char : ¥

For the most part the explanation is "valid" punctuation in a numeric "sting". Currency, signs and separators. Can't explain Horizontal Tab(9), Newline(10), Vertical Tab(11), Form Feed (12) and carriage Return (13) other than their presence at the beginning of a numeric "string" does allow the sting to evaluate as numeric.

select

isnumeric(char(13)+'123' )Characters above 127 will be different based on the codepage currently loaded.