Splitting names in parts with SQL

Problem: slit names in a family name-part and an personal name part using SQL.

Here is a sample of the names we want to split

mysql> select name from foo limit 10;
+------------------------------+
| name                         |
+------------------------------+
| David Fredriksson            |
| Jenny Lindberg               |
| Annika Arvidsson             |
| Anders Hansson               |
| Liselotte Axelsson           |
| Per-Olof Orrbeck Tollesson   |
| Anette Axelsson              |
| Pernilla Larsson             |
| Linda Nyqvist                |
| Maria Palmqvist              |
+------------------------------+
10 rows in set (0.01 sec)

mysql> select substr(name, instr(name, ' '), length(name)) as "family name" from foo limit 10;
+--------------------+
| family name        |
+--------------------+
|  Fredriksson       |
|  Lindberg          |
|  Arvidsson         |
|  Hansson           |
|  Axelsson          |
|  Orrbeck Tollesson |
|  Axelsson          |
|  Larsson           |
|  Nyqvist           |
|  Palmqvist         |
+--------------------+
10 rows in set (0.00 sec)

mysql> select substr(name, 1, instr(name, ' ')) as "personal name" from foo limit 10;
+---------------+
| personal name |
+---------------+
| David         |
| Jenny         |
| Annika        |
| Anders        |
| Liselotte     |
| Per-Olof      |
| Anette        |
| Pernilla      |
| Linda         |
| Maria         |
+---------------+
10 rows in set (0.00 sec)

comments powered by Disqus

Back to the index

Blog roll

R-bloggers, Debian Weekly

---

Last modified: oktober 17, 2019