Back to General discussions forum
Hey, I was not on site for a while, and decided to pick something really simple (at least I thought so). I chose Introducing SQL
But I have problems with this task. My query is:
SELECT UPPER(first_name || '_' || SUBSTR(middle_names, 1, 1) || '_' || last_name)
FROM users WHERE middle_names NOT LIKE '% %' AND suffix IS NULL
But Abraham Lincoln does not show up with this query, can anyone tell me why?
For this particular row, middle name is missing, so SQL can neither ascertain that this middle name is indeed NOT LIKE '% %', nor select it inside SUBSTR, and therefore skips the entire row.
After you've resolved this issue, you will have another small fix to make in your query, by the way. :)
Thanks, zelevin! These null values are very tricky undeed:
SELECT NULL NOT LIKE '% %' AS Oops
Oops
----
null
I thought that Oops will evaluate to True, because NULL definitely not looks like % %, but I was wrong...
Will try to fix these issues now.
Solved the puzzle.
Turned out to be pretty challenging task.
I ended up with a kilometer length solution, but at least it works somehow :)