Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Exclude rows with blanks in a particular field Expand / Collapse
Author
Message
Posted Wednesday, May 01, 2013 2:33 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 9:33 AM
Points: 292, Visits: 1,131
I know this is a popular one and I've Googled it but I haven't found anything that has worked yet, so apologies if this is incredibly obvious.

I'm trying to amend a view to exclude records that are either null or blank in a field called CURRENT_POINT

...
and CURRENT_POINT is not null
and DATALENGTH(CURRENT_POINT)> 0

The nulls are fine, but I'm still getting the blanks. I hoped DATALENGTH would resolve that but it hasn't.

As I said, I'm sorry if it's obvious. Can anybody advise me please?
Post #1448339
Posted Wednesday, May 01, 2013 2:43 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:41 AM
Points: 6,748, Visits: 12,843
Beatrix Kiddo (5/1/2013)
I know this is a popular one and I've Googled it but I haven't found anything that has worked yet, so apologies if this is incredibly obvious.

I'm trying to amend a view to exclude records that are either null or blank in a field called CURRENT_POINT

...
and CURRENT_POINT is not null
and DATALENGTH(CURRENT_POINT)> 0

The nulls are fine, but I'm still getting the blanks. I hoped DATALENGTH would resolve that but it hasn't.

As I said, I'm sorry if it's obvious. Can anybody advise me please?


Here's a little test harness to see what DATALENGTH returns with different values:
SELECT 
CURRENT_POINT,
[LEN] = LEN(CURRENT_POINT),
[DATALENGTH] = DATALENGTH(CURRENT_POINT)
FROM (
SELECT rn = 1, CURRENT_POINT = CAST(' ' AS VARCHAR(15)) UNION ALL
SELECT 2, SPACE(3) UNION ALL
SELECT 3, NULL UNION ALL
SELECT 4, '' UNION ALL
SELECT 5, 'Something here'
) d
WHERE 1 = 1



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1448343
Posted Wednesday, May 01, 2013 2:46 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 1:49 PM
Points: 32,768, Visits: 14,929
Is not > '' working?

CREATE TABLE MyTable
( current_point VARCHAR(20)
)
;
INSERT MyTable SELECT 'Test'
INSERT MyTable SELECT ''
INSERT mytable SELECT ' '
INSERT mytable SELECT NULL
GO
SELECT * FROM mytable
-- 4 rows

SELECT * FROM mytable
WHERE Current_Point IS NOT NULL
-- 3 rows

SELECT * FROM mytable
WHERE Current_Point IS NOT NULL
AND current_point > ' '
-- 1 rows

SELECT * FROM mytable
WHERE Current_Point IS NOT NULL
AND current_point > ''
-- 1 row








Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1448345
Posted Wednesday, May 01, 2013 2:52 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 9:33 AM
Points: 292, Visits: 1,131
Thanks for the replies so far.

Steve Jones - SSC Editor (5/1/2013)
Is not > '' working?


Using that doesn't exclude the rows with blanks in, if that's what you mean?

I'm sorry, I'm not quite sure what the previous post is asking.

I don't even know why some of these fields have blanks instead of nulls. It seems to be a data entry issue.
Post #1448349
Posted Wednesday, May 01, 2013 3:03 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 9:33 AM
Points: 292, Visits: 1,131
Ah, ChrisM@Work, I think I get what you were saying now.

and LEN(LTRIM(RTRIM(CURRENT_POINT))) > 0

Seems to have fixed it.


Thanks all!
Post #1448352
Posted Wednesday, May 01, 2013 3:27 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:41 AM
Points: 6,748, Visits: 12,843
Beatrix Kiddo (5/1/2013)
Ah, ChrisM@Work, I think I get what you were saying now.

and LEN(LTRIM(RTRIM(CURRENT_POINT))) > 0

Seems to have fixed it.


Thanks all!


LEN ignores leading spaces, you don't need to trim. However, Steve's suggestion is likely to perform faster because CURRENT_POINT isn't wrapped in a function, which in most cases would prevent an index on CURRENT_POINT from being used for the filter.


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1448360
Posted Wednesday, May 01, 2013 3:31 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 9:33 AM
Points: 292, Visits: 1,131
Cool, thanks. I've amended it to.

and LEN(CURRENT_POINT) > 0

Post #1448361
Posted Wednesday, May 01, 2013 3:39 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:41 AM
Points: 6,748, Visits: 12,843
Beatrix Kiddo (5/1/2013)
Cool, thanks. I've amended it to.

and LEN(CURRENT_POINT) > 0



Is there any reason not to use this?
CURRENT_POINT > ' '



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1448364
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse