Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
convert sql column from nvarchar to numeric
I have a SQL database called BBKTrial, a table called dbo.sheet1$. a column called [as-am]. This column is nvarchar(225) format. I need to convert it to numeric.
How can I do this??
How can I do this??
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
see CAST() or CONVERT()
Conversion in that direction (string >>> to >>> number) will fail is any non-numeric data is in that column
either of these will do it.
CONVERT( [as-am] as decimal(12,3) )
or
CAST( [as-am] , decimal(12,3) )
you can change the numeric type to suit whatever it is you need.
BUT. this conversion can cause failures.
Conversion in that direction (string >>> to >>> number) will fail is any non-numeric data is in that column
either of these will do it.
CONVERT( [as-am] as decimal(12,3) )
or
CAST( [as-am] , decimal(12,3) )
you can change the numeric type to suit whatever it is you need.
BUT. this conversion can cause failures.
You can protect against conversion errors using iSNUMERIC() but this completely safe.
e.g.
select CONVERT( [as-am] as decimal(12,3) )
from that_table
where ISNUMERIC([as-am]) = 1
a result of 1 from ISNUMERIC() indicates the parameters holds numeric information but this can include items such as currency symbols
We could advise better if you supplied this:
select distinct [as-am]
from that_table
where ISNUMERIC([as-am]) = 0
e.g.
select CONVERT( [as-am] as decimal(12,3) )
from that_table
where ISNUMERIC([as-am]) = 1
a result of 1 from ISNUMERIC() indicates the parameters holds numeric information but this can include items such as currency symbols
We could advise better if you supplied this:
select distinct [as-am]
from that_table
where ISNUMERIC([as-am]) = 0
The above works for a query. If you need to change the column data type in the table to numeric, then...
BUT this will only work if all values can be converted to numeric in the first place. Use Paul's ISNUMERIC() function to test.
ALTER TABLE dbo.sheet1$
ALTER COLUMN as-am numeric(19,4)
-- change the 19 and 4 to whatever percision and scale you want
BUT this will only work if all values can be converted to numeric in the first place. Use Paul's ISNUMERIC() function to test.
Another way, you can use below query
SELECT
CASE WHEN ISNUMERIC([as-am]) =1 THEN CONVERT(numeric,[as-am]) ELSE 0 END
FROM <your table>
SELECT
CASE WHEN ISNUMERIC([as-am]) =1 THEN CONVERT(numeric,[as-am]) ELSE 0 END
FROM <your table>
The Experts above already told you how to convert so I'll focus in another thing in your question.
You said: "a table called dbo.sheet1$. a column called [as-am]. This column is nvarchar(225) format."
This seems to me a table exported from an Excel spreadsheet using the Import/Export wizard. When you export data by the wizard, you can define the datatype of the destination column, so next time you can do it immediatly during the Import/Export process so no convertion will be needed after the Export.
You said: "a table called dbo.sheet1$. a column called [as-am]. This column is nvarchar(225) format."
This seems to me a table exported from an Excel spreadsheet using the Import/Export wizard. When you export data by the wizard, you can define the datatype of the destination column, so next time you can do it immediatly during the Import/Export process so no convertion will be needed after the Export.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial