NULL vs. blank fields in SSMS or Flat File

Question

Post reply

NULL vs. blank fields in SSMS or Flat File

pshvets

SSCommitted

Points: 1850
More actions
January 26, 2009 at 6:26 pm

#383807

Hello SQL server gurus,

I am relatively new to SQL server, my background is Oracle/Teradata on UNIX platform so probably my approach is biased on the latter.

Can someone explain what is the difference between NULL and blank fields in SSMS grid?

I handled NULL values with ISNULL function but no blanks (which I assume are NULLs too)

My goal is to substitute all NULL values with appropriate default values.

Thanks a lot in advance!

Pit.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Rajesh Jonnalagadda Ten Centuries Points: 1032 More actions · Answer 1

Rajesh Jonnalagadda

Ten Centuries

Points: 1032

January 27, 2009 at 12:53 am

#933032

try this

ISNULL(Value, '8779')

Eric SSCommitted Points: 1803 More actions · Answer 2

When you are looking at the SSMS grid nulls and cells with whitespace are not the same thing. In fact cells that look like white space or empty strings can be many things. Run the following:



CREATE TABLE #test(

SomeCol VARCHAR(10)

,WhatItIs VARCHAR(20)

)

-- load some interesting data

INSERT INTO #test (

SomeCol

,WhatItIs

)

SELECT '     ' AS SomeCol, '5 spaces' AS WhatItIs UNION ALL

SELECT '   '  AS SomeCol, '3 spaces' AS WhatItIs  UNION ALL

SELECT ''  AS SomeCol, 'empty string' AS WhatItIs  UNION ALL

SELECT CHAR(13) AS SomeCol, 'carriage return' AS WhatItIs  UNION ALL

SELECT CHAR(9) AS SomeCol, 'tab character' AS WhatItIs  UNION ALL

SELECT NULL AS SomeCol, 'null' AS WhatItIs  

-- before you run this, what do you think the result will be??

SELECT

SomeCol

,WhatItIs

,CASE WHEN SomeCol IS NULL THEN 'Yes' ELSE 'No' END AS IsItNull

,LEN(SomeCol) AS SomeColLen

,DATALENGTH(SomeCol) AS SomeColDatalength

FROM #test

-- were you right or were you surprised?

-- clean up

DROP TABLE #test

pshvets SSCommitted Points: 1850 More actions · Answer 3

Eric,

Thanks a lot for your example. It makes sense. I wrongfully assumed that my data is clean.

It looks like I got empty strings in my table.

Can you suggest how to replace all empty strings with let's say '#'?

LTRIM(RTRIM()) or REPLACE don't seem to do the work. I ran the query against your sample table:

SELECT

SomeCol

,WhatItIs

,CASE WHEN SomeCol IS NULL THEN 'Yes' ELSE 'No' END AS IsItNull

,LEN(SomeCol) AS SomeColLen

,DATALENGTH(SomeCol) AS SomeColDatalength

,ISNULL(LTRIM(rtrim(SomeCol)), '#') SomeColTrim

,REPLACE(SomeCol, ' ', 'white_spaces') SomeColReplace3spaces

,ASCII(SomeCol) as SomeColASCII

from test

Thanks for your help!

Pit.

Eric SSCommitted Points: 1803 More actions · Answer 4

You can use a case expression:



SELECT

SomeCol

,WhatItIs

,CASE WHEN LEN(SomeCol) = 0 THEN '#' ELSE SomeCol END AllSpacesToPound

,CASE WHEN DATALENGTH(SomeCol) = 0 THEN '#' ELSE SomeCol END EmptyStringToPound

,CASE WHEN SomeCol IS NULL THEN 'Yes' ELSE 'No' END AS IsItNull

,LEN(SomeCol) AS SomeColLen

,DATALENGTH(SomeCol) AS SomeColDatalength

FROM #test

Just curious, are you importing data from flat files?

pshvets SSCommitted Points: 1850 More actions · Answer 5

Thank you for suggestion, I should've guessed it myself... 🙂

That's correct - I am loading data from pipe delimited files.

I've never had this issue on UNIX/Oracle/Teradata environment where I extract the data into flat files and then load it into DB. TRIm had always worked for me.

Just wondering why it is different on SQL Server?

Regards,

Peter.