SQL easy regex extraction

Input

You'll have a table like the following:

namegreeting

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.

namegreetinguser_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 iser_id will be having varchar type

 

Solution:

SELECT 
  name, 
  greeting, 
  SUBSTRING(greeting FROM '#(\d{1,20})') AS user_id 
FROM 
  greetings;

 

The above regular expression can be changed as follows.

'#(\d+)'

'#([0-9]+)'

 

Result:

name	greeting	user_id
Marcy Hamill	Hola que tal #4702665	4702665
Hosea Boyle	Bienvenido 45454545 tal #470815 BD. WA470815	470815
Sen. Chuck Wunsch	#Orlando DuBuque #5090243154 Prof. Selina Frami	5090243154
Waneta Breitenberg	#Roderick Kuhic #2591504965 #Patrick Connelly	2591504965
Venessa Ryan	#Anderson Mohr #6703010129 William Rodriguez	6703010129