Exclude rows with blanks in a particular field

Question

Exclude rows with blanks in a particular field

Beatrix Kiddo

SSC-Dedicated

Points: 32407
More actions
May 1, 2013 at 2:33 am

#394699

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?

Viewing 8 posts - 1 through 7 (of 7 total)

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

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

Steve Jones - SSC Editor SSC Guru Points: 736193 More actions · Answer 2

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

Beatrix Kiddo SSC-Dedicated Points: 32407 More actions · Answer 3

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.

Beatrix Kiddo SSC-Dedicated Points: 32407 More actions · Answer 4

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!

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

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

Beatrix Kiddo SSC-Dedicated Points: 32407 More actions · Answer 6

Cool, thanks. I've amended it to.

and LEN(CURRENT_POINT) > 0

🙂

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

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