• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 333
  • Last Modified:

Problem using substr() function in MySql

Hi

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

This is string B:
#/schema#/CT[Items]#/sequence[0]#/element[item]#/CT[]#/sequence[0]#/element[productImage]

And this is A:
#/schema#/CT[Items]#/sequence[0]#/element[item]#/CT[]#/sequence[0]

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
0
abaqasah
Asked:
abaqasah
  • 5
  • 2
1 Solution
 
Tomas Helgi JohannssonCommented:
Hi!

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

http://dev.mysql.com/doc/refman/5.6/en/string-functions.html#function_replace

Regards,
    Tomas Helgi
0
 
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.

Regards
0
 
abaqasahAuthor Commented:
Hi

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

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

Cheers
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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.
0
 
abaqasahAuthor Commented:
Hi yodercm

Applying
SUBSTRING(StringB,"#",-1)
will not result anything.

You probably mean
SUBSTRING_INDEX(StringB,"#",-1)

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):
/element[productImage]
Which is not what I want

Regards,
Abdullah
0
 
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.
0
 
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
0

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now