How to find records containing only digits? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

How to find records containing only digits?

Author

Topic

Cornelius19
Starting Member

30 Posts

Posted - 2008-03-12 : 16:26:16

Hello,

I would like to find all the records that contain only digits. So far, I have this:

SELECT *
FROM tmp1
WHERE (word LIKE N'[0-9]')

It returns only ten results, each containing a single digit. What I need is to find all the records of any length containing only digits, like '378', '2005', etc. but not records containing both digits and other stuff (e.g. letters) like 'I95' or 'P2P'.

Any idea?

Cornelius

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-03-12 : 17:31:46

isnumeric function should help

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-03-12 : 17:36:03

SELECT *
FROM tmp1
WHERE isnumeric(word)=1

Cornelius19
Starting Member

30 Posts

Posted - 2008-03-12 : 17:44:53

quote:
Originally posted by sakets_2000

SELECT *
FROM tmp1
WHERE isnumeric(word)=1

Thanks a lot, that is exactly what I was looking for!

Cornelius

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-03-12 : 18:00:03

Depends on what you consider Numeric:

DECLARE @Yak TABLE (Val VARCHAR(50))

INSERT @Yak
SELECT '1'
UNION ALL SELECT '1,000'
UNION ALL SELECT '5e3'
UNION ALL SELECT '100'
UNION ALL SELECT '100.00'
UNION ALL SELECT '9781297'
UNION ALL SELECT '9781e297'
UNION ALL SELECT '978w1297'
UNION ALL SELECT 'asdfg'


SELECT *
FROM @Yak
WHERE ISNUMERIC(val) = 1

SELECT *
FROM @Yak
WHERE Val NOT LIKE '%[^0-9]%'

Cornelius19
Starting Member

30 Posts

Posted - 2008-03-12 : 18:07:55

Thanks. This is a useful distinction.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-03-12 : 18:16:00

playing on the same note..
how would you handle something like '1101,00'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-13 : 02:27:22

select * from table1 where col1 not like '%[^0-9]%'

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources