asked on

MS SQL 2012 SELECT without "empty" records

Hi

I'm trying to SELECT into a new table where a particular column contains data which ignores the rows where a certain field is empty/null/whatever!

I've tried various ways, including the following:

SELECT *
INTO dbo.T_1213_OnlySSN
FROM T_1213_SSN
WHERE student_support_number IN (SELECT student_support_number FROM T_1213_SSN);

or

WHERE student_support_number Is Not Null OR student_support_number <> '';

or

WHERE student_support_number Is Not Null OR len(ltrim(student_support_number)) <> 0;

I've read around and think the field will be empty rather than Null, but none of these are working and I end with a load or rows where the student_support_number (nvarchar) has nothing in it.

Any ideas?

Thanks
Sarah

PortletPaul

why not try counting to help narrow down the problem?

select
count(*) as count_all
, count(case when student_support_number Is Null then student_support_number end) as count_null
, count(case when student_support_number = '' then student_support_number end) as count_empty
, count(case when student_support_number Is Not Null or student_support_number <> '' then student_support_number end) as count_not_null_empty
from T_1213_SSN

what is this result?

ASKER CERTIFIED SOLUTION

ScuzzyJo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PortletPaul

!=

<>

are equivalents (both do "not equal")

which you stated you had already tried...
WHERE student_support_number Is Not Null OR student_support_number <> '';

glad it's resolved.

Louis01

Hi Sarah
Just a note on your SQL above. The

WHERE student_support_number != ''

would still return NULL values. Perhaps it is safer to use this:

SELECT *
INTO dbo.T_1213_OnlySSN
FROM T_1213_SSN
WHERE IsNull(student_support_number, '') != ''

Open in new window

NO points please, NO objections to Q being closed.

Zberteoc

The problem is that you have some unprintable charatcers in your column which are not equal to '' even though it apperas empty and len(col) still is >0

In order to find thoese rows you can run:

select * from where PATINDEX('%[^0-9a-z]%', student_support_number)>0

This will return all the rows where student_support_number has anything that is not a digit between 0 and 9 or a letter between a and z. It is case insensitive.

If that query returns something you will have to clean the values in the student_support_number column.

ScuzzyJo

ASKER

Hi Guys

Louis - when I looked at the data, I realised there shouldn't be any null values. It comes from a download from a massive student database which won't allow them. Thanks for the comment though, I'll bear it mind as it's bound to be relevant later on.

Z - thanks for yours too. SSN's themselves can only contain digits and alphabet characters between a and z. I take your point though and will watch out for it in future.

Thanks
Sarah

ScuzzyJo

ASKER

Resolved this with the help of someone else.

MS SQL 2012 SELECT without &quot;empty&quot; records

MS SQL 2012 SELECT without "empty" records