CODEWITHSUNDEEP
mysqlselectsubstring
user9371654
user9371654

Reputation: 2408

substring_index does not take exact prefix

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

Answers (3)

forpas
forpas

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

Supun Kanda
Supun Kanda

Reputation: 75

use regex. Much more simpler.

SELECT mycol FROM mytable WHERE mycol REGEXP '^http://.+';

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 176324

You could add check for delimeter:

SELECT `mycol`,
   IF(instr(mycol,'http://') > 0, substring_index(`mytable`.`mycol`,'http://',-1),NULL)
FROM mytable;

db<>fiddle demo

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

Related Questions