How can I split a name column into first and last names?
(1) By anonymous on 2020-08-11 14:56:12 [link] [source]
How can I split a name column into first and last names and view a specific first name using queries? I have a table called people and I need to find out how many people in this table (which has a column called 'names') have the first name 'MERYL' using the Execute SQL tab.
(2) By Larry Brasfield (LarryBrasfield) on 2020-08-11 15:56:17 in reply to 1 [source]
When you (or somebody else) wrote
CREATE TABLE people (name TEXT, ...)
CREATE TABLE people (firstname TEXT, restofname TEXT, ...)
, the decision was made then to either never care about distinguishing components of a name or be forced to desperate measures to maybe extract them.
You can, if desperate enough, use the instr() and substr() functions, documented at Built-In Scalar SQL Functions, to extract the first space-delimited clump of characters from your "names" column. I leave the mechanics to you, (being not so desperate myself.)
Be warned that queries involving such expressions may be slow because they force a full table scan and evaluation of those string functions for each row.
You may want to investigate database normalization for future work.
(3) By Gunter Hick (gunter_hick) on 2020-08-11 15:57:31 in reply to 1 [link] [source]
This is usually accomplished by storing first and last names in separate fields in the first place. String handling is usually not performed within SQL queries, but you can use the INSTR() function to locate the first separator character and the SUBSTR() function to return what is before and after this location respectively and maybe the TRIM() function to remove extraneous whitespace. Also consider using LIKE 'meryl%' (assuming first names are stored first int he field) to locate names begining with 'meryl' in any combination of case (unless you have executed pragma case_sensitive_like, in which case you must provide the exact case your are looking for
(5) By Keith Medcalf (kmedcalf) on 2020-08-11 20:50:26 in reply to 3 [link] [source]
That would be "like 'meryl %'" unless you wanted 'merylithium tarnish' or 'meryltok dogwood' to satisfy the criteria. Of course if the person had only one name that was 'meryl' this would not work either.
This is why "Given" and "Family" names are usually stored separately. There is also nothing which requires the "first token" to be the given name. In many cultures the "first token" is the family name or even a dead word.
(4) By David Raymond (dvdraymond) on 2020-08-11 16:04:45 in reply to 1 [link] [source]
Real world names are complicated, someone will inevitably write a long reply or post a link on the topic.
where upper(substr(names, 1, instr(names, ' ') - 1)) = 'MERYL'
I haven't checked if that works and doesn't raise an error on things like the empty string, or text fields without a space, etc. But it's a place to start