Return only numeric rows - 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 2005 Forums

Transact-SQL (2005)

Return only numeric rows

Author

Topic

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2010-04-01 : 11:17:42

Hi,

I have a nvarchar column with numeric values and string values
sample:
0000091206017076863
0000091206017076864
0000091206017076935
0000091206017076936
0000091206017076937
Telephonewire
Partials

I need a query that will only return the numeric rows:
0000091206017076863
0000091206017076864
0000091206017076935
0000091206017076936
0000091206017076937

Thanks in advance.

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2010-04-01 : 11:37:14

[code]select * from tblFoo where col not like '%[^0-9]%'[/code]

Harsh Athalye
http://www.letsgeek.net/

DaleTurley
Yak Posting Veteran

76 Posts

Posted - 2010-04-01 : 11:37:40

Try SELECT * FROM Table WHERE ISNUMERIC(Column) = 1

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2010-04-01 : 11:41:39

ISNUMERIC() is not reliable and returns true for characters like D, E,. and tab character.

Harsh Athalye
http://www.letsgeek.net/

DaleTurley
Yak Posting Veteran

76 Posts

Posted - 2010-04-01 : 11:42:36

Can't say I've ever experienced that with ISNUMERIC???

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-01 : 12:09:06

quote:
Originally posted by DaleTurley

Can't say I've ever experienced that with ISNUMERIC???

check this


SELECT val,ISNUMERIC(val) AS [Numeric]
FROM
(
SELECT '$' AS Val UNION ALL
SELECT '1e3'UNION ALL
SELECT '3d5' 
)t


output
------------------------------------
val  Numeric
---- -----------
$    1
1e3  1
3d5  1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

PackRat
Starting Member

26 Posts

Posted - 2010-04-01 : 14:33:50

I forgot that about ISNUMERIC, looks like I've got some code to update

_____________________________
wrote this on my TRS-80 COCO4

<PakRat/>

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-01 : 14:38:20

quote:
Originally posted by PackRat

I forgot that about ISNUMERIC, looks like I've got some code to update

_____________________________
wrote this on my TRS-80 COCO4

<PakRat/>

Glad that you recollected it

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2010-04-02 : 09:57:10

Thanks all for the replies.
Isnumeric works great.

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-02 : 11:01:24

quote:
Originally posted by collie

Thanks all for the replies.
Isnumeric works great.

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

Make sure you check whole of data and ensure you dont have patterns like that specified before present

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources