SQLite: trim usage

Question

My goal is to extract the domain out of given URL.

For that end I use the following:

select distinct ltrim(rtrim('https://www.youtube.com/watch?v=...', '/'), 'https://')

The result I get is:

www.youtube.com/watch?v=...

While the following is expected:

www.youtube.com

How can the above be achieved?

Note:

I notices that the trim function works differently than I expected.

• select distinct ltrim('https://www.youtube.com/watch?v...', 'youtu') returns the same string without any change.
• Trying to trim only the slash by select ltrim('https://www.youtube.com/watch?v...', '/') returns the same string as well.

Any explainations are welcomed.

Answer 1

At the end I didn't use trim but substr as offered.

The following worked:

select replace(substr(substr(<url>, instr(<url>, '//')+2),0,instr(substr(<url>, instr(<url>, '//')+2),'/')),'.','')

select replace(substr(substr(<url>, instr(<url>, '//www.')+6),0,instr(substr(<url>, instr(<url>, '//www.')+6),'/')),'.','')

Answer 2

Trim only removes the given characters at the beginning and/or end of the string. You'll need substr and instr. (https://www.sqlite.org/lang_corefunc.html)

But the best option is probably to fix this in your code before saving it to the database.