Extracting Last Name and First Name in Snowflake (POSITION Function)

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

Extracting Last Name and First Name in Snowflake (POSITION Function)

Extracting Last Name and First Name (Space-Delimited)

Related articles