?
Solved

MS SQL 2012 SELECT without "empty" records

Posted on 2013-11-19
7
Medium Priority
?
235 Views
Last Modified: 2013-11-24
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
0
Comment
Question by:ScuzzyJo
7 Comments
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39659063
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?
0
 

Accepted Solution

by:
ScuzzyJo earned 0 total points
ID: 39659151
Hi Paul

Thanks for your help.  I've resolved this with the help of someone and have used:

SELECT *
INTO T_1213_OnlySSN
FROM T_1213_SSN
WHERE student_support_number != ''

This seems to work fine.  I'm not sure why/how, but will look into that.

Thanks
Sarah
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39659166
!=

<>

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.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 11

Expert Comment

by:Louis01
ID: 39659191
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.
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 39659570
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.
0
 

Author Comment

by:ScuzzyJo
ID: 39659663
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
0
 

Author Closing Comment

by:ScuzzyJo
ID: 39672346
Resolved this with the help of someone else.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question