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

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store