Avatar of gvamsimba
gvamsimba
Flag for United Kingdom of Great Britain and Northern Ireland asked on

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
Microsoft SQL Server 2005Microsoft SQL Server 2008

Avatar of undefined
Last Comment
gvamsimba

8/22/2022 - Mon
Kent Olsen

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
Steven Kribbe

select convert(Decimal(28,16),convert(float, <your value>))

Adjust precision of the decimal to your needs.
Jim Horn

>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

Your help has saved me hundreds of hours of internet surfing.
fblack61
gvamsimba

ASKER
Hi Jim, mine is sql server 2008 and the data is multiple rows
gvamsimba

ASKER
Hi Kdo, your query has a syntax error.

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'from'.
gvamsimba

ASKER
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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Kent Olsen

Sorry -- double isn't a SQL Server type.  :(  Use float instead.

 SELECT cast (replace ('12,345.99', ',', '') as float)
Jim Horn

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

gvamsimba

ASKER
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.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Jim Horn

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.
gvamsimba

ASKER
Hi Jim,

But I need to get all those records which has a value of less than 50
gvamsimba

ASKER
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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
gvamsimba

ASKER
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)
Jim Horn

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

Kent Olsen

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)
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
gvamsimba

ASKER
Hi Jim, that worked.. Please give me the condition now to filter less than 50 which looks better
Jim Horn

Explain 'filter less than 50'.
gvamsimba

ASKER
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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Jim Horn

>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

Peter Chan

Try

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

Open in new window

SOLUTION
gvamsimba

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
gvamsimba

ASKER
well, the solution should work for any conditions...less than or greater than should not really matter...
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
gvamsimba

ASKER
Good