RunnerNwizard
asked on
NULL all blank recordsets in my SQL Sever 2005 table
I have a table which I created by importing a flat file (using SSIS). The table is set to all nvarchar datatypes at the moment. I would like to convert the columns of the table into specific datatypes mosty numeric. However before I do this I would like to convert all the blank spaces into NULLS.
In addition, I would like to be able to perform a CREATE > INSERT a New Table out of this data and cast all the columns to numeric datatypes, while leaving the blanks NULL and not '0'.
In addition, I would like to be able to perform a CREATE > INSERT a New Table out of this data and cast all the columns to numeric datatypes, while leaving the blanks NULL and not '0'.
Blank spaces or blank fields or fields with blank spaces?
To NULL all fields that contain blank spaces:
UPDATE MyTable SET MyField = NULL WHERE TRIM(MyField) = ''
To NULL all fields that contain blank spaces:
UPDATE MyTable SET MyField = NULL WHERE TRIM(MyField) = ''
ASKER
In order for me to cast a numeric on the nvarchar datatypes i currently have, I have found that that I have to convert using the following line of code below.
What this also does is turn all the EMPTY recordsets into 0.000000, what I would like is to turn the empty cells into NULL
What this also does is turn all the EMPTY recordsets into 0.000000, what I would like is to turn the empty cells into NULL
SELECT cast(cast([COLUMN] AS float) As numeric (15,6)).
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
UPDATE tableName SET columnName=null WHERE columnName=''
as for the second part of your question it would be something like this..
SELECT CAST(column1 as int) as column1, CAST(column2 as numeric(10,2)) as column2
INTO newTableName
FROM tableName