January 16, 2015 at 12:40 pm
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.
January 16, 2015 at 12:45 pm
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
January 16, 2015 at 12:47 pm
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.)
January 16, 2015 at 1:08 pm
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.
January 16, 2015 at 1:17 pm
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
January 16, 2015 at 1:22 pm
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.
January 16, 2015 at 1:22 pm
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
January 16, 2015 at 1:37 pm
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
January 16, 2015 at 1:37 pm
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
January 16, 2015 at 3:16 pm
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".
January 16, 2015 at 3:18 pm
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".
January 17, 2015 at 11:33 am
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
Change is inevitable... Change for the better is not.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply