Codewars SQL

Kata 7kyu

SQL easy regex extraction

Input

You’ll have a table like the following:

name

greeting

Austin Gaylord

Hola que tal #4702665

Kacie Zulauf

Bienvenido 45454545 tal #470815 BD. WA470815

Output

In this practice you’ll need to extract from the greeting column the number preceeded by the # symbol and place it in a new column named user_id.

name

greeting

user_id

Austin Gaylord

Hola que tal #4702665

4702665

Kacie Zulauf

Bienvenido 45454545 tal #470815

BD. WA470815

470815

NOTE: To keep it simple assume that the user_id will be having varchar type

Hint: The input table is “greetings”. The syntax for regex extraction is: “regexp_matches”

Solution:

SELECT name,
       greeting,
       unnest(regexp_matches(greeting, '#(\d+)')) as user_id
FROM greetings; 

Link

Reference