Alphanumeric Check? - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Alphanumeric Check?

Author

Topic

bbasir
Yak Posting Veteran

76 Posts

Posted - 2008-03-26 : 15:11:41

I have a field where data can be numeric or alphanumeric. Is there a way to get only the Alpha Numeric data?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-03-26 : 15:21:02

where isnumeric(yourcolumn) = 0
note however that e and d are also valid in numeric strings
so isnumeric('123d4') and isnumeric('123e4') will return true.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

nr
SQLTeam MVY

12543 Posts

Posted - 2008-03-26 : 15:21:13

depends on what you mean by numeric.
how about
select * from tbl where fld like '%[^0-9]%'

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nr
SQLTeam MVY

12543 Posts

Posted - 2008-03-26 : 15:23:01

quote:
Originally posted by spirit1

where isnumeric(yourcolumn) = 0

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

As I said - depends what you mean by numeric.
That will allow 5e6
mine will not allow 8.5 - and 5e6 maybe you consider that numeric.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-03-26 : 16:10:43

quote:
Originally posted by nr

depends on what you mean by numeric.
how about
select * from tbl where fld NOT like '%[^0-9]%'

Small correction. :)

nr
SQLTeam MVY

12543 Posts

Posted - 2008-03-26 : 16:20:07

Nope - should be like. OP wants the alphanumeric data not the numeric only.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-03-26 : 16:27:38

Opps my bad, I miss read the requirements. Here is a sample for teh OP, to see the differences:

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]%'

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

SELECT *
FROM @Yak
WHERE Val NOT LIKE '%[^a-z]%'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-02 : 03:19:32

Another method

SELECT *
FROM @Yak
WHERE isnumeric(Val+'.d0')=0

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources