SQLite Studio Question for Substr making some of my values disappear?

Question

I'm working with a free dataset from Kaggle to learn SQL further and I've hit a spot where I am stuck. I'm working with an NFL Draft dataset that has player names listed like this: FirstName LastName\UserName

However, some of the rows are simply this: FirstName LastName

I wrote this code and have had some success: SELECT position, substr(Player,0,instr(Player,'\')) AS Player_Name

This specfic code works great on any rows that are formatted like FirstName LastName\UserName but for any rows that are formatted like this FirstName LastName it returns a blank for the Player_Name.

Any tips on how I can fix this to show the FirstName LastName ONLY on my query for both ways?

Answer 1

INSTR returns 0 if the char is not in the string

Supplying a length of 0 causes an output from SUBSTR of zero length string

This is why your name disappears

---

What we can do is convert the 0 to something else:

SUBSTR(player, 0, COALESCE(NULLIF(INSTR(player, '/'), 0), 9999)

Now if INSTR returns 0, NULLIF converts it to NULL, and COALESCE converts the NULL to 9999

SQLite doesn't care that 9999 is beyond the end of the string (make it bigger if it isn't) and returns the whole rest of string, so effectively the SUBSTR is a non-op

---

There are other ways to skin the cat:

CASE WHEN player LIKE '%/%' THEN SUBSTR(...) ELSE player END

but they might search the string twice, for lower overall performance.. It might not matter, if it's a handful of values queried infrequently, you might prefer a more readable form over a (theoretically) more performant one