SQLTeam.com | Weblogs | Forums

First character of a string is a number

sql2012

#1

Hello,

How to check if the first character of a string is a number.

Thanks for your help.


#2
SELECT c
FROM (VALUES
      ('abcb')
     ,('1bcd')) t(c)
WHERE
   ISNUMERIC(LEFT(c,1))=1;

#3

Alternatively,

SELECT c
FROM (VALUES
      ('abcb')
     ,('1bcd')) t(c)
WHERE
	c LIKE '[0-9]%'

#4

Careful now... you should NEVER mistake ISNUMERIC() as meaning "ISALLDIGITS" because that's not what it's for. Try the following as an example.

SELECT c
FROM (VALUES
      ('abcb')
     ,('1bcd')
     ,(',bcd')
     ,('$bcd')
     ,('\bcd')
    ) t(c)
WHERE
   ISNUMERIC(LEFT(c,1))=1
;

#5

Given

it might be that following characters are, also, important ... so the test might need to be

   c LIKE '[0-9]%'
OR c LIKE '[-+][0-9]%'

There again I might be overthinking the problem ... :heart_eyes:


#6

Good point @JeffModen. I wasn't event thinking about the ISNUMERIC behavorio when I suggested that OP use the LIKE '[0-9]%' construct. I was only thinking about avoiding the function calls and ability to use indexes if any.


#7

hi all

Please see the definition of isnumeric in microsoft docs

See the NOTE section

IMAGE