Check If a number contains characters

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Check If a number contains characters

Author

Topic

vladimir_grigoro
Yak Posting Veteran

62 Posts

Posted - 2003-02-11 : 05:23:38

Hi All,

I want to check in sp, which has input parameter char(8) if the string contains only numbers or it has characters as well.
Example:
'00123456'
'12345678'
'123456a4'
and etc.

Thanks in advance.

The Rebel

Andraax
Aged Yak Warrior

790 Posts

Posted - 2003-02-11 : 06:26:22

Have you tried the ISNUMERIC function?

select ISNUMERIC('12345678')

returns 1

select ISNUMERIC('123456a4')

returns 0

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2003-02-11 : 08:55:27

except that.......select ISNUMERIC('1e2')also returns 1
as does...select ISNUMERIC('1.2')

ISNUMERIC validates that the expression is a NUMBER (in the mathematical sense of things)...and NOT 'are all characters in this string numbers in the range 0-9'

it's a known 'feature' of this command...(and it applies in VB(6) as well)....as long as you understand what it will and will not do, they use it as you will.

Bambola
Posting Yak Master

103 Posts

Posted - 2003-02-11 : 09:21:02

Try this:

Declare @x varchar(3)

SELECT @x = '1e3'
IF NOT (@x like '%[^0-9]%')
SELECT 'only numbers'
ELSE
SELECT 'characters as well'

With this approach you will also avoid overflow problems in case
you are using something bigger than int.

Bambola.

vladimir_grigoro
Yak Posting Veteran

62 Posts

Posted - 2003-02-12 : 09:06:58

Thank you all for your help. With these two checks everything seems OK.

Thanks.

The Rebel

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources