convert varchar to int with decimal values in tsql server 2008

Hi, I have a columns which is a varchar. However, I have some some numbers as well as decimals.

the example values are 9,10,223,345.99

I tried converting to int as well as float but neither did not work . they gave errors.

can anyone please give me correct tsql code ?

Many Thanks
gvamsimbaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kent OlsenData Warehouse Architect / DBACommented:
Hi gvamsimba,

You'll need to remove the punctuation from the string.

  SELECT replace (column_name, ',', '') from mytable;

Then just recast the result.

  SELECT cast (replace (column_name, ',', '') as double) from mytable;


Good Luck!
Kent
0
Steven KribbeSoftware EngineerCommented:
select convert(Decimal(28,16),convert(float, <your value>))

Adjust precision of the decimal to your needs.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>they gave errors.
Please tell us what these errors are.  Mind readers we ain't.

>the example values are 9,10,223,345.99
Are is this a single value '9,10,223,345.99 ', or multiple rows with values 9, 10, .. ?

It's possible that there are some non-numeric values that are throwing an error, such as '3 1/2', 'banana', or '9-10'.  To view any values that can't convert to int, SQL 2012 and higher you can use TRY_CONVERT
SELECT column_name
FROM table_name
WHERE TRY_CONVERT(int, column_name) IS NULL

Open in new window

0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

gvamsimbaAuthor Commented:
Hi Jim, mine is sql server 2008 and the data is multiple rows
0
gvamsimbaAuthor Commented:
Hi Kdo, your query has a syntax error.

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'from'.
0
gvamsimbaAuthor Commented:
Hi skribbe , your query is giving an error as below..

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to float.
0
Kent OlsenData Warehouse Architect / DBACommented:
Sorry -- double isn't a SQL Server type.  :(  Use float instead.

 SELECT cast (replace ('12,345.99', ',', '') as float)
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Ok.  With 2008 you can use ISNUMERIC, which returns a 1 if the value is in numeric format, and 0 if it isn't.
SELECT column_name
FROM table_name
WHERE ISNUMERIC(column_name) = 0

Open in new window

0
gvamsimbaAuthor Commented:
Hi Jim, the original errors which I got for float and int are as follows

Error converting data type varchar to float.

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '3100.00' to data type int.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Any thousands separating commas would also throw an..error
Declare @x varchar(10) = '12,456'
SELECT CAST(@x as int) 

Open in new window

Also spaces or empty strings '' may throw an error, although for some reason I can't replicate this on my SSMS 2012 box.
0
gvamsimbaAuthor Commented:
Hi Jim,

But I need to get all those records which has a value of less than 50
0
gvamsimbaAuthor Commented:
Declare @x varchar(10) = '12,456'
SELECT CAST(@x as int)

Msg 245, Level 16, State 1, Line 2
Conversion failed when converting the varchar value '12,456' to data type int.
0
gvamsimbaAuthor Commented:
Hi Jim, even a decimal value will give you an error..and this is what my problem is

Declare @x varchar(10) = '12.456'
SELECT CAST(@x as int)
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Got it, and I have the same result in my 2012 box.
Give this a whirl, and before we're completely done we'll need to address rounding.
Declare @x varchar(10) = '12.456'
SELECT CASE
   WHEN ISNUMERIC(@x) = 1 THEN CAST(CAST(@x as numeric(19,4)) as int)
   ELSE NULL end

Open in new window

0
Kent OlsenData Warehouse Architect / DBACommented:
SELECT cast (cast (replace (column_name, ',', '') as float) as integer) from mytable;

to test:

  SELECT cast (cast (replace ('12,345.99', ',', '') as float) as integer)
0
gvamsimbaAuthor Commented:
Hi Jim, that worked.. Please give me the condition now to filter less than 50 which looks better
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Explain 'filter less than 50'.
0
gvamsimbaAuthor Commented:
Jim, what I meant was , can you please provide me with a working query like above which give me the data where the values are less than 50
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>where the values are less than 50
Okay, that would be a simple WHERE clause.  To avoid duplicating all of the CAST I'll throw it in a subquery...
SELECT a.column_name
FROM (
   SELECT CASE
      WHEN ISNUMERIC(column_name) = 1 THEN CAST(CAST(column_name as numeric(19,4)) as int)
      ELSE NULL end as column_name
   FROM your_table) a
WHERE a.column_name < 50

Open in new window

0
HuaMin ChenProblem resolverCommented:
Try

select cast(replace('9,10,223,345.99',',','') as decimal(18,3))

Open in new window

0
gvamsimbaAuthor Commented:
Hi Jim,

in case if I want to filter the data above 50, unfortunately your final solution ignores values between 50 and 51..However the below solution recommended by my colleague has worked. Many thanks for your help though..

                  SELECT a.column_name
FROM (
   SELECT column_name
   FROM your_table
   where IsNumeric(Answer) = 1) a
WHERE cast (ltrim(rtrim(t.answer)) as float ) > 50.0
order by cast (ltrim(rtrim(t.answer)) as float )
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>Please give me the condition now to filter less than 50 which looks better
>unfortunately your final solution ignores values between 50 and 51
>WHERE cast (ltrim(rtrim(t.answer)) as float ) > 50.0
That's because your first statement above said 'less than 50', which is mutually exclusive with the second statement.   Further, it appears multiple experts answered your questions as originally asked, before the additional requirements.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gvamsimbaAuthor Commented:
well, the solution should work for any conditions...less than or greater than should not really matter...
0
gvamsimbaAuthor Commented:
Good
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.