?
Solved

convert empty string to null

Posted on 2006-05-08
5
Medium Priority
?
2,934 Views
Last Modified: 2008-01-09
I'm tryin to insert some records from table1 to table2 using ssis.  table1 has column1 which may contain some empty strings or padded with spaces, how do I transform those empty strings to nulls when I'm inserting into table 2.

tahnks


thanks
sopheak

0
Comment
Question by:sopheak
5 Comments
 
LVL 5

Expert Comment

by:morisce
ID: 16632824
use nullif operator :
select ... nullif(COL,' ') ...
0
 
LVL 4

Expert Comment

by:ch70357
ID: 16633025
Try:

SELECT
     CASE LEN(YourColumnName) WHEN 0 THEN NULL ELSE YourColumnName END AliasColumnNameHere
FROM YourTableName

Even if empty strins are there (even if it were entered a multiple blank spaces), the LEN returned will still be 0
0
 
LVL 28

Accepted Solution

by:
imran_fast earned 800 total points
ID: 16636954
use derived column (data flow task) for those and use the code  to change this column to null.
0
 

Author Comment

by:sopheak
ID: 16639789
imran fast,

That what I have been trying to do, but cann find the code to do it.  Do you have the code to put in the derived column.

thanks
sopheak
0
 

Author Comment

by:sopheak
ID: 16641044
thanks I figured out the code
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question