CODEWITHSUNDEEP
sql-servert-sql
Sam109
Sam109

Reputation: 9

Recognise replacement of """ as 'null' in SQL server

I have currently just written a query to replace all double quotes across my input data (from a csv file that is pipe delimited but also has double quotes around each column). I am trying to simultaneously coalesce the first column (which is a nvarchar column), but I cannot get my coalesce statement to work as upon removing the double quotes SQL doesn't recognise the column as blank (doesn't come up as NULL).

Would really appreciate any help on what I can do to make SQL recognise that Column1, once rid of "", is now full of blanks and therefore should pull data from columns 2, 3 or 4!

Insert Into [mytable] 
Select COALESCE (REPLACE (Column1,'"', ''), REPLACE (Column 2, '"', ''), 
REPLACE (Column3, '"', ''), REPLACE (Column4, '"', ''))
From [datasource]

Upvotes: 0

Views: 904

Answers (3)

Pete Carter
Pete Carter

Reputation: 2731

Insert Into [mytable] 
Select NULLIF(COALESCE (REPLACE (Column1,'"', ''), REPLACE (Column 2, '"', ''), 
REPLACE (Column3, '"', ''), REPLACE (Column4, '"', '')),'')
From [datasource]

Upvotes: 1

Zohar Peled
Zohar Peled

Reputation: 82474

Quite cumbersome, but you can use NULLIF with LTRIM and:

Select COALESCE (NULLIF(LTRIM(REPLACE (Column1,'"', '')), ''), 
                 REPLACE (Column 2, '"', ''), 
                 REPLACE (Column3, '"', ''), 
                 REPLACE (Column4, '"', ''))
From [datasource]

LTRIM will remove all the leading white spaces from your string. if it only contains white spaces, LTRIM will return an empty string. NULLIF will return null if the two arguments passed to is are equal.

My example is only for the first column, use it on the other columns if you need.

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521053

You could use a CASE expression:

INSERT INTO [mytable] 
SELECT
    CASE WHEN LEN(REPLACE(COALESCE(Column1, ''), '"', '')) > 0
         THEN REPLACE(COALESCE(Column1, ''), '"', '')
         WHEN LEN(REPLACE(COALESCE(Column2, ''), '"', '')) > 0
         THEN REPLACE(COALESCE(Column2, ''), '"', '')
         CASE WHEN LEN(REPLACE(COALESCE(Column3, ''), '"', '')) > 0
         THEN REPLACE(COALESCE(Column3, ''), '"', '')
         WHEN LEN(REPLACE(COALESCE(Column4, ''), '"', '')) > 0
         THEN REPLACE(COALESCE(Column4, ''), '"', '')
         ELSE '' END
FROM [datasource]

I feel that some of the ugliness of this query is due to your data model. Ideally you would not be storing doubke quotes at all, just the actual content. Then, we could just use NULL or empty string to decide which content to insert.

Upvotes: 0

Related Questions