convert sql column from nvarchar to numeric

Curtis Long
Curtis Long used Ask the Experts™
on
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??
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
EE Topic Advisor
Most Valuable Expert 2014
Awarded 2013
Commented:
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.
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
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
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015
Commented:
The above works for a query. If you need to change the column data type in the table to numeric, then...
ALTER TABLE dbo.sheet1$
ALTER COLUMN as-am numeric(19,4)  
-- change the 19 and 4 to whatever percision and scale you want

Open in new window


BUT this will only work if all values can be converted to numeric in the first place.  Use Paul's ISNUMERIC() function to test.
Ensure you’re charging the right price for your IT

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!

Dung DinhDBA and Business Intelligence Developer
Commented:
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>
Vitor MontalvãoIT Engineer
Distinguished Expert 2017
Commented:
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.

Author

Commented:
How do you define the data type during import??  I do not see that option in the wizard
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
In the Import Wizard when you chose the worksheets and the destination table, click on "Edit Mappings..." button and will pop-up a new screen where you can change column names and data types.

EditMappings.PNG

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial