CODEWITHSUNDEEP
sqlsql-server-2005
Tam Tran
Tam Tran

Reputation: 13

isnumeric function

I have table with data a mixture of alpha and numeric, I'm trying to find the way to extract value only if it's numeric 

PSDcode2:
========
631302
631308
631309
631301
 STATE
 STATE
ON TWP

My select statement will work just fine, but I just is there better way of doing this? thanks 

case when PS.PSDcode2 in (' STATE','ON TWP') then 'NULL' else PS.PSDcode2  end as PSDcode2,

Upvotes: 1

Views: 63

Answers (2)

Chandana Kumara
Chandana Kumara

Reputation: 2645

For SQL:-

SELECT PSDcode2 FROM [Table_Name] WHERE ISNUMERIC([PSDcode2]) = 1

check this: https://msdn.microsoft.com/en-us/library/ms186272.aspx

OR

Select PSDcode2 [Table_Name] WHERE PSDcode2 NOT LIKE '%[^0-9]%' AND [PSDcode2]!=''

For Oracle:-

SELECT PSDcode2 FROM [Table_Name] WHERE REGEXP_LIKE(PSDcode2,'^[[:digit:]]+$');

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93754

I will go with NOT LIKE approach it works well for any data 

Select * [Table_Name] WHERE PSDcode2 NOT LIKE '%[^0-9]%'

Upvotes: 1

Related Questions