Reputation: 2408
I have a table contains strings. Some of them starts with https://
and some starts with http://
. I want to extract those that starts with http://
(without s
). Please note I do not want to use LIKE
statement because of another conflict in my plan to treat this string. So if I have the following items in a column called mycol
in mytable
:
https://111.com/
https://www.222.com/en-gb/
I make this query:
SELECT `mytable`.`mycol`, substring_index(`mytable`.`mycol`,'http://',-1)
I still get these strings in the results:
https://111.com/
https://www.222.com/en-gb/
Why? since my query is looking for http://
not https://
, why do I get results start with https://
? In this simple example, it should return nothing as there is no string starts with http://
want to extract the string
Upvotes: 0
Views: 283
Reputation: 164234
I want to extract those that starts with http://
If you don't want want to use LIKE you can use left():
select right(columnname, length(columnname) - 7) from tablename
where left(columnname, 7) = 'http://'
You don't need to know the length of the string.
All you want is extract the part of the string after the 1st 7 chars.
The length of this part is:
length(columnname) - 7
So use right().
Upvotes: 0
Reputation: 75
use regex. Much more simpler.
SELECT mycol FROM mytable WHERE mycol REGEXP '^http://.+';
Upvotes: 1
Reputation: 176324
You could add check for delimeter:
SELECT `mycol`,
IF(instr(mycol,'http://') > 0, substring_index(`mytable`.`mycol`,'http://',-1),NULL)
FROM mytable;
When the SUBSTRING_INDEX
function cannot find delim string it will return original string instead of NULL
.
SELECT substring_index('abc','.',-1)
=>
abc
Upvotes: 0