how to Convert varchar to Numeric

Question

how to Convert varchar to Numeric

mkpanchal

SSC Veteran

Points: 266
More actions
July 14, 2008 at 1:02 am

#71638

In my table i have data in varchar. I am creating the view table in that want data in numeric.
I have tried following query but it gives error message "error converting data type varchar to numeric"
SELECT strTimeStamp, CAST(strItem AS numeric(18, 2)) AS Expr1 FROM tblDataEntry AS tblDataEntry
I also tried with
SELECT strTimeStamp, CONVERT(decimal,strItem) AS Expr1 FROM tblDataEntry AS tblDataEntry
Please tell me how to convert the same so i can use in MSCHART for generating graph.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

There are some values in that column that cannot be converted to numeric. You're going to have to find those values and either remove them or exclude them from the query.

You can use IsNumeric to check,however there are some values that return true from isnumeric, but cannot be converted to numeric

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

mkpanchal SSC Veteran Points: 266 More actions · Answer 2

Yes i found some values there as non numeric so i have added the condition like WHERE (tblItem.strItem NOT LIKE '%[A-Z+:-_]%')

Thanks for the same.

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 3

Obviously, that WHERE clause won't catch everything... but this will...

WHERE tblItem.strItem NOT LIKE '%[^0-9]%'

The circumflex (^) also means "NOT".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 4

... heh... and before anyone even thinks of suggesting ISNUMERIC, try this...

SELECT ISNUMERIC('$1000') UNION ALL

SELECT ISNUMERIC('1,000') UNION ALL

SELECT ISNUMERIC('3d2') UNION ALL

SELECT ISNUMERIC('2e3')

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Actually, I did suggest ISNumeric, with the warning that it's not perfect. Does your like allow for - and .?

Here's one that caught me very, very badly the other day...

SELECT ISNUMERIC (' - 0.00 ') -- 1

SELECT CAST(' - 0.00 ' AS NUMERIC (7,2)) -- error

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 6

GilaMonster (7/14/2008)
Actually, I did suggest ISNumeric, with the warning that it's not perfect. Does your like allow for - and .?

No... but those can easily be added while excluding a broader range of characters than the Where clause previously offered by the OP.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)