CODEWITHSUNDEEP
sqlsql-server-2008
kanth
kanth

Reputation: 341

How to select numeric value from nvarchar column in sql server?

I have table like 

Create table Producttbl ( sno nvarchar(100),sname nvarchar(200),price nvarchar(100))

Values are

sno    sname price
1       aaa   1.50
2       ccc   5.30 
abc     xxx   abc
3       dsd   kkk 
nn      dss   5.1

Price column is nvarchar it accept all kind of data like string or numeric.

From that table, I want to select results like this:

   sno        sname  price
    1           aaa   1.50
    2           ccc   5.30

Please help me.

Upvotes: 0

Views: 12295

Answers (2)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239654

You presumably want sno values which consist of digits and only digits (otherwise, you need to specify which "numeric" types you wish to accept):

select * from ProductTbl where sno not like '%[^0-9]%'

We use a double negative check, to exclude sno values which contain a non-digit character.

Upvotes: 2

garnertb
garnertb

Reputation: 9584

Have you tried using the isnumeric function?

SELECT sno,sname
FROM ProductTbl
WHERE ISNUMERIC(sno)=1

You should read up on the isnumeric documentation to be sure that it returns the results you expect. (see comments below).

Upvotes: 2

Related Questions