How can I trim everything between certain characters in sqlite

Question

I am using a select query to get the email Ids. However in email ID column except email there is a text in between ; and : which I want to trim so that I can only get the email Ids nothing else.

For example:-

;some text here: [email protected] ---- this is the data, and I want to select only email ID not the text before that.

How can I achieve it?

Thanks.

question from:https://stackoverflow.com/questions/66058485/how-can-i-trim-everything-between-certain-characters-in-sqlite

Answer 1

Assuming the unwanted text would always appear before the email address, then you need only use substring:

SELECT col, TRIM(SUBSTR(col, INSTR(col, ':') + 1)) AS email
FROM yourTable;

Demo