ScuzzyJo
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
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_
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
!=
<>
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.
<>
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.
Hi Sarah
Just a note on your SQL above. The
NO points please, NO objections to Q being closed.
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, '') != ''
NO points please, NO objections to Q being closed.
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.
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.
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
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
ASKER
Resolved this with the help of someone else.
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?