SQLite Forum

Nulls or blanks

Nulls or blanks

(1) By adrian762 on 2020-12-02 13:39:42 [link] [source]


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

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 [link] [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]


Thanks for that but have tried that to no avail


(7) By adrian762 on 2020-12-03 10:32:21 in reply to 5 [link] [source]


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??



(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 [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]


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