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



RunnerNwizardAsked:
Who is Participating?
 
mofoworkingConnect With a Mentor Commented:
Wrap it in a case statement and only cast the rows with values and turn the blank values into NULLs



SELECT CASE WHEN TRIM(COLUMN) = '' THEN NULL ELSE cast(cast([COLUMN] AS float) As numeric (15,6)) END

Open in new window

0
 
CMYScottCommented:
if you have a few columns to do, it would be like:

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
0
 
DireOrbAntCommented:
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) = ''
0
 
RunnerNwizardAuthor Commented:
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


SELECT cast(cast([COLUMN] AS float) As numeric (15,6)). 

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.