substring query

[sandy1028]

I have to get the id from tablename  matching the string after 3rd '/'.

 

i.e, return the id based in string 2163724v3d0324.

 

How can I write a query,

 

SELECT id FROM `tablename` WHERE eid LIKE SUBSTRING_INDEX('2020/08/03/2163724v3d0324','/',3)

 

[fenway]

And what does that call to S_I return?

[bowett]

From the MySQL manual:

 

SUBSTRING_INDEX(str,delim,count)

 

Returns the substring from string str before count occurrences of the delimiter delim. If count is positive, everything to the left of the final delimiter (counting from the left) is returned. If count is negative, everything to the right of the final delimiter (counting from the right) is returned. SUBSTRING_INDEX() performs a case-sensitive match when searching for delim.

 

So your query was nearly correct, try this:

 

SELECT id FROM `tablename` WHERE eid LIKE SUBSTRING_INDEX('2020/08/03/2163724v3d0324','/',-3)