Problem using substr() function in MySql


I need to extract a string A from string B where B is stored in MySQL table.

This is string B:

And this is A:

The delimiter in my case is '#' and I need to remove it along with the following characters '/element[productImage]'.

I tried different functions like SUBSTR(str, pos, len), POSITION(substr IN str), and REVERSE(str) but cannot solve the problem.

Note that index of the last occurrence of '#' is unknown. And I can not find a way to locate the last index of the '#' character (like lastIndexOf() function in JAVA).
I believe that there is a way to do it by reversing the whole string first, cutting the unnecessary part then reversing again to get the desired result.

Can any one help please
Who is Participating?
abaqasahConnect With a Mentor Author Commented:

I found a solution in other place:
Here it is:

LEFT(str, CHAR_LENGTH(str) - LOCATE('#', REVERSE(str)))

Tomas Helgi JohannssonCommented:

If you know the string to remove then you can use

REPLACE(org_string, string_to_replace, replace_str)

where replace_str is the '' (empty string).

Note that this replaces all occurences of the string_to_replace in org_string

    Tomas Helgi
abaqasahAuthor Commented:
Hi TomasHelgi

I don't know exactly the string_to_replace. All I know is the org_string and the delimiter '#' which should be used to remove the last part '#/element[productImage]' from the org_string.

The actual problem is how to locate the last occurrence of '#' character.

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Cornelia YoderArtistCommented:
The SUBSTRING_INDEX() function has a parameter that allows you to select the LAST occurrence of a string.

So if you use something like this:

StringA = SUBSTRING(StringB,"#",-1)

that will give you everything to the left of the last # in String B.
abaqasahAuthor Commented:
Hi yodercm

will not result anything.

You probably mean

And that solution actually return everything in the right part of '#' and NOT in the left.
Result of applying this in my string B (mentioned in the question):
Which is not what I want

Cornelia YoderArtistCommented:
You are right, I got in a rush and omitted a main part of the line.

String A =  SELECT SUBSTRING(StringB FROM 1 FOR (LENGTH(StringB)-1-LENGTH(SUBSTRING_INDEX(StringB,"#",-1)))) FROM Table...

That works as you wish.
abaqasahAuthor Commented:
Hi  yodercm

That is another solution :)
I already apply the first solution
LEFT(str, CHAR_LENGTH(str) - LOCATE('#', REVERSE(str)))
Found it easy to write

Thanks anyway
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.