Nulls or blanks
(1) By adrian762 on 2020-12-02 13:39:42 [link] [source]
Hi I have a query where i have created a view and i want to extract every personid number where either there is a zero value shown or (I assume) its a null or blank The following is an example where I would like to get the personid that matches the criteria of either having nothing there or a zero. So I would like to select 1770, 3090, 15 and 21 PersonID FatherID MotherID 2146 2149 2148 169 2157 2158 1770 2168 0 3090 3789 0 15 21 I have tried to see if they are blanks and I am getting nowhere Help please Many thanks Adrian Mullins
(2) By Larry Brasfield (LarryBrasfield) on 2020-12-02 14:09:24 in reply to 1 [link] [source]
Have you tried adding something like
where MotherID=0 or MotherID='' or MotherID is null;
?
(3) By anonymous on 2020-12-02 15:04:08 in reply to 2 [link] [source]
Wouldn't trim(MotherID)='' be a better idea than without trim()?
(4) By Larry Brasfield (LarryBrasfield) on 2020-12-02 15:47:39 in reply to 3 [link] [source]
Using trim() might be better, depending on what the OP's data means. I had the impression that the difficulty was more fundamental. And because there are few ways of acquiring data that result in space sequences, I thought to not complicate things any more for the OP.
(5) By Ryan Smith (cuz) on 2020-12-02 15:53:57 in reply to 3 [source]
Well sure, but that depends on the data cleanliness...
The hope is the OP understands his data, he didn't mention any problems with rogue spaces.
On that topic, you can use TRIM to cull not only spaces, but any kind of unwanteds, I usually have this in unclean data:
TRIM(someCol, CHAR(9)|CHAR(10)|CHAR(13)|CHAR(32)|CHAR(160))
That Char 160 (0xA0) can especially be tricky, just cause it walks like a space and talks like a space, doesn't mean it definitely is a space.
There are more Unicode "space" characters too in case your data is of the international unicode-containing ilk.
Link: http://jkorpela.fi/chars/spaces.html
(It's not an https:// link so did not want to embed it in the markdown.)
(6) By adrian762 on 2020-12-03 10:30:53 in reply to 2 [link] [source]
Larry
Thanks for that but have tried that to no avail
adrian
(7) By adrian762 on 2020-12-03 10:32:21 in reply to 5 [link] [source]
Ryan
They are not nulls nor spaces but cant seem to detect them. Thanks for your answer and will try.
By the way, what is OP? Old person??
Thanks
Adrian
(8) By Larry Brasfield (LarryBrasfield) on 2020-12-03 11:22:23 in reply to 7 [link] [source]
It is an acronym for Original Poster.
(9) By Simon Slavin (slavin) on 2020-12-03 11:46:33 in reply to 7 [link] [source]
It is important that you figure out what values are in those fields. So use
SELECT FatherID, typeof(FatherID), length(FatherID), hex(FatherID) FROM <tablename>
and tell us what it produces for those troublesome fields.
(10) By adrian762 on 2020-12-03 12:32:02 in reply to 9 [link] [source]
Hi
Thanks for that. I did as you say and it came out as Null for type and no results for the others
As a beginner I think my problem was trying to get a query that picked where fatherid was null and also where motherid was null. I could not do that. I think I tried to do too much with one line of code but needed to use several lines
I have now succeeded in doing what I wanted another way.
I have learned from you as the above code will be of great help in the future
Many thanks to all who have helped me and I apologise for my shortcomings which caused you all some time
Again
Thanks
Adrian