Learn the techniques to avoid forgery and phishing attacks and the types of attacks an application or network may face.
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
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
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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
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
select convert(Decimal(28,16),con
Adjust precision of the decimal to your needs.
Adjust precision of the decimal to your needs.
>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
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
Hi Kdo, your query has a syntax error.
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'from'.
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'from'.
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.
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to float.
Sorry -- double isn't a SQL Server type. :( Use float instead.
SELECT cast (replace ('12,345.99', ',', '') as float)
SELECT cast (replace ('12,345.99', ',', '') as float)
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
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.
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.
Any thousands separating commas would also throw an..error
Declare @x varchar(10) = '12,456'
SELECT CAST(@x as int)
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.
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.
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)
Declare @x varchar(10) = '12.456'
SELECT CAST(@x as int)
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.
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
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)
to test:
SELECT cast (cast (replace ('12,345.99', ',', '') as float) as integer)
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
>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...
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
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 )
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 )
>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.
>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.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial