The best way to include zero string and null value

Question

The best way to include zero string and null value

adonetok

SSCrazy Eights

Points: 8395
More actions
August 22, 2014 at 7:59 am

#290058

I need to search on varchar column including zero length string and null value.
What is the best way to do it?

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

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

Eirikur Eiriksson SSC Guru Points: 182920 More actions · Answer 1

adonetok (8/22/2014)
I need to search on varchar column including zero length string and null value.
What is the best way to do it?

Something like this? (code from another reply adopted to the question)

😎

USE tempdb;

GO

DECLARE @test-2 TABLE

(

TEST_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED

,TEST_STRING VARCHAR(50) NULL

);

INSERT INTO @test-2(TEST_STRING)

VALUES

('I am a bad Gal')

,('I am a good Gal')

,('This is an old horse')

,('This is a good horse')

,('I am a good boy')

,('I am a naugthy boy')

,('')

,(NULL);

SELECT

*

FROM @test-2 T

WHERE ISNULL(T.TEST_STRING,'') LIKE '%good%';

Results

TEST_ID TEST_STRING

----------- ---------------------

2 I am a good Gal

4 This is a good horse

5 I am a good boy

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

Hey Eirikur,

You don't even need that function to get the exact same results.

SELECT

*

FROM @test-2 T

WHERE T.TEST_STRING LIKE '%good%';

To test if a string is not empty or null, you can do it in a single where clause without functions

SELECT

*

FROM @test-2 T

WHERE T.TEST_STRING > ''; --Validates empty and nulls

To look for empty and null values at once, you'll need 2 clauses (or a function)

SELECT

*

FROM @test-2 T

WHERE T.TEST_STRING = ''

OR T.TEST_STRING IS NULL;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

adonetok SSCrazy Eights Points: 8395 More actions · Answer 3

Thank yo for help.

If I put script like below, does it include all records?

SELECT

*

FROM @test-2 T

WHERE T.TEST_STRING in ('red', 'green') or T.TEST_STRING = ''

OR T.TEST_STRING IS NULL

Eirikur Eiriksson SSC Guru Points: 182920 More actions · Answer 4

adonetok (8/22/2014)
Thank yo for help.
If I put script like below, does it include all records?
SELECT
*
FROM @test-2 T
WHERE T.TEST_STRING in ('red', 'green') or T.TEST_STRING = ''
OR T.TEST_STRING IS NULL

If the dataset consists of those values, yes.

😎

USE tempdb;

GO

DECLARE @test-2 TABLE

(

TEST_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED

,TEST_STRING VARCHAR(50) NULL

);

INSERT INTO @test-2(TEST_STRING)

VALUES

('red')

,('green')

,('')

,(NULL);

SELECT

*

FROM @test-2 T

WHERE T.TEST_STRING in ('red', 'green') or T.TEST_STRING = ''

OR T.TEST_STRING IS NULL

Eirikur Eiriksson SSC Guru Points: 182920 More actions · Answer 5

Luis Cazares (8/22/2014)
Hey Eirikur,
You don't even need that function to get the exact same results.

You are right (of course) Luis, I just quickly copied an example I still had open since this morning:blush:

😎