Finding a number in a field - SQL Server Forums

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

Finding a number in a field

Author

Topic

rjc08
Starting Member

3 Posts

Posted - 2008-11-20 : 08:01:05

I have column for surnames. I want to run a check on that column to make sure that there are no numbers in any of the field.

I tried this, but didn't seem to work:

select surname from acounts where surname > 1

acollins74
Yak Posting Veteran

82 Posts

Posted - 2008-11-20 : 08:09:37

If you are looking for entire records that are numeric then use the isnumeric() function.

something like..

select * from yourTable
where isnumeric(yourColumn) = 1

This will return all valid numeric datatypes.

karthickbabu
Posting Yak Master

151 Posts

Posted - 2008-11-20 : 08:17:54

SELECT * FROM TableName WHERE ColName NOT LIKE REPLICATE('[0-9]',LEN(ColName))

====================================================
you realize you've made a mistake, take immediate steps to correct it.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-11-20 : 08:18:15

ISNUMERIC sometimes gets things wrong. I think you can use LIKE for this. (assuming it's a VARCHAR field?)


DECLARE @foo TABLE (
	[field] VARCHAR(10)
	)

INSERT @FOO
      SELECT 'abcdef'
UNION SELECT 'abc1ed'
UNION SELECT '123'
UNION SELECT '0'
UNION SELECT '44 BAR'

SELECT
	f.*
FROM
	@foo f
WHERE
	f.[field] NOT LIKE '%[^0-9]%'

N.B : ISNUMERIC doesn't get things *wrong* sorry : but it might calss something you wouldn't expect as a number!
Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

karthickbabu
Posting Yak Master

151 Posts

Posted - 2008-11-20 : 08:22:24

SELECT * FROM TableName WHERE ColName LIKE '%[^0-9]%'

====================================================
you realize you've made a mistake, take immediate steps to correct it.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-11-20 : 08:32:17

quote:
Originally posted by karthickbabu

SELECT * FROM TableName WHERE ColName LIKE '%[^0-9]%'

====================================================
you realize you've made a mistake, take immediate steps to correct it.

That would return '9 BAR' which I don't think OP wants. You need NOT LIKE '%[^0-9]%' Which I already posted.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-11-20 : 08:42:49

And -- I've just thought of decimals! damn it.

OK then you want


DECLARE @foo TABLE (
	[field] VARCHAR(10)
	)

INSERT @FOO
      SELECT 'abcdef'
UNION SELECT 'abc1ed'
UNION SELECT '123'
UNION SELECT '0'
UNION SELECT '44 BAR'
UNION SELECT '44.2213'
UNION SELECT '12.32as'

SELECT
	f.*
FROM
	@foo f
WHERE
	f.[field] NOT LIKE '%[^0123456789.]%'

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-20 : 09:00:17

also see this

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/enhanced-isnumeric-function.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources