Extracting Last Name and First Name (Space-Delimited)
Target data
imazaj#XSMALL@SQLDB.PUBLIC>select name from CUSTOMERS limit 5;
+-------------+
| NAME |
|-------------|
| 石塚 拓 |
| 長坂 賢介 |
| 野中 裕之 |
| 髙松 龍 |
| 小倉 慎太郎 |
+-------------+
Use the POSITION function. Equivalent to STRPOS in PostgreSQL, INSTR in Oracle.
POSITION — Snowflake Documentation
POSITION( <expr1>, <expr2> [ , <start_pos> ] ) POSITION( <expr1> IN <expr2> ) Arguments Required: expr1 A string or binary expression representing the value to search for. expr2 A string or binary expression representing the value to search. Optional: start_pos A number indicating the position to start the search (1 represents the start of expr2). Default: 1
select
name,
SUBSTR(name, 1, POSITION(' ', name) -1) as lastname,
SUBSTR(name, POSITION(' ', name)) as firstname
from
customers
limit 5;
imazaj#XSMALL@SQLDB.PUBLIC>select
name,
SUBSTR(name, 1, POSITION(' ', name) -1) as lastname,
SUBSTR(name, POSITION(' ', name)) as firstname
from
customers
limit 5;
+-------------+----------+-----------+
| NAME | LASTNAME | FIRSTNAME |
|-------------+----------+-----------|
| 石塚 拓 | 石塚 | 拓 |
| 長坂 賢介 | 長坂 | 賢介 |
| 野中 裕之 | 野中 | 裕之 |
| 髙松 龍 | 髙松 | 龍 |
| 小倉 慎太郎 | 小倉 | 慎太郎 |
+-------------+----------+-----------+
5 Row(s) produced. Time Elapsed: 0.974s