what else can you search for instead of col1 = '' or col1 = ' ' or col1 is null?

Question

what else can you search for instead of col1 = '' or col1 = ' ' or col1 is null?

JP10

SSCarpal Tunnel

Points: 4372
More actions
January 16, 2015 at 12:40 pm

#314568

I have a column that is empty, but when I search for that column with col1 = '' or col1 = ' ' or col1 is null or col1 like '% %' I still can't find the value for this column?
I have cut and pasted the value to see if there are spaces in the value and there are 0 spaces. I'm stuck on what the value can be and how I can search for it?
Appreciate the help.

Viewing 12 posts - 1 through 11 (of 11 total)

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

jphillips 46546 SSC-Addicted Points: 439 More actions · Answer 1

when you cut and pasted did you put it into an editor that can show ASCII characters? Just want to make sure there isn't something like a CR or LF in the field.

Other than that try a query like

Where ltrim(rtrim(col1)) = ''

James Phillips
Sr. Consultant
Pragmatic Works

LoudClear SSC Eights! Points: 999 More actions · Answer 2

Or you can

select ASCII( col1 )

to see what nonprintable characters are in there.

(Keep in mind, that will only show you the first one.)

JP10 SSCarpal Tunnel Points: 4372 More actions · Answer 3

I get a result of 0. Looks like a ASC null value? Is there a way to cast a ASC value null to certain char() data type? Right now looks like my code I have a cast() function to a certain char() data type length. But, when it hit's this value it doesn't seem to cast it? Appreciate it.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 4

Where ltrim(rtrim(col1)) = ''

I see this code ALL THE TIME at clients. Can someone tell me why both would be needed?? 😎

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

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

TheSQLGuru (1/16/2015)
Where ltrim(rtrim(col1)) = ''
I see this code ALL THE TIME at clients. Can someone tell me why both would be needed?? 😎

It's not needed.

It's probably a misconception coming from other systems as a string full of (common) spaces is the same as an empty string. In Oracle is not the same and that gave me headaches when I started working with it.

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

jphillips 46546 SSC-Addicted Points: 439 More actions · Answer 6

Kevin,

I guess it's out of habit when working with fields and searching for actual text and wanting to eliminate white space from the beginning and end. No real purpose when looking for a blank text.

James Phillips
Sr. Consultant
Pragmatic Works

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 7

Just wanted to make sure I wasn't having a brain cloud!! 🙂

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 8

Butt, the paperwork is simple, although it can smear your reputation and cause quit a stink.

Jeff, that is SOOO over the top!! 😛

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

ScottPletcher SSC Guru Points: 101099 More actions · Answer 9

TheSQLGuru (1/16/2015)
Where ltrim(rtrim(col1)) = ''
I see this code ALL THE TIME at clients. Can someone tell me why both would be needed?? 😎

Why would either be "needed"? Just col1 = '' should do it, since:

'' = space(1) = space(5) = space(10) = ...

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

ScottPletcher SSC Guru Points: 101099 More actions · Answer 10

You could check for ascii 0 in a the column like so:

WHERE

col1 LIKE '%' + CHAR(0) + '%'

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 11

TheSQLGuru (1/16/2015)
Butt, the paperwork is simple, although it can smear your reputation and cause quit a stink.
Jeff, that is SOOO over the top!! 😛

Heh... yeah, sorry. I really bottomed out there, huh? 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)