Solved

How do I select text between two delimiters in MySQL?

Posted on 2013-05-20
20
2,453 Views
Last Modified: 2013-05-21
Hi,

I am trying to select some text between two string delimiters in MySQL, the delimiters:

I have tried following this but haven't been able to get it to work with my scenario:
http://stackoverflow.com/questions/149690/search-for-text-between-delimiters-in-mysql
http://www.electrictoolbox.com/mysql-select-locate-substring/

My scenario is:

Example string:
index.php?option=com_mtree&link_id=84485&task=viewlink

What I want to do is extract the value of link_id only which is 84485 so in theory my left side delimiter should be "link_id=" and my right side delimiter should be "&"

I have constructed the following query which doesn't return the desired results:

SELECT
  SUBSTR(url_real,
    LOCATE('link_id=', url_real)+9,
      (CHAR_LENGTH(url_real) - LOCATE('&',REVERSE(url_real)) - LOCATE('&', url_real)))
FROM health3_acesef_urls
0
Comment
Question by:jwleys
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 5
  • 4
  • +1
20 Comments
 
LVL 25

Assisted Solution

by:chaau
chaau earned 250 total points
ID: 39183010
use this:

SELECT
  SUBSTR(url_real,
    LOCATE('link_id=', url_real)+8,
      (CASE WHEN LOCATE('&', url_real, LOCATE('link_id=', url_real)+9) > 0 
                THEN LOCATE('&', url_real, LOCATE('link_id=', url_real, 0)+9) - LOCATE('link_id=', url_real) + 6
                ELSE  LENGTH(url_real) - LOCATE('link_id=', url_real)+7 END))
FROM health3_acesef_urls 

Open in new window


SQL Fiddle
0
 

Author Comment

by:jwleys
ID: 39183052
Thank you very much for your input :)

Definitely on the right track, your example query does work and achieves the desired result if I append a LIKE clause to identify my example record, however, if I just run the query without a where / like clause it doesn't work for all cases. I think what I need to take into consideration in this query in terms of troubleshooting is that the delimiters and data I want to extract with my select statement do not exist on every record only some:

SELECT SUBSTR( url_real, LOCATE( 'link_id=', url_real ) +8, (

CASE WHEN LOCATE( '&', url_real, LOCATE( 'link_id=', url_real ) +9 ) >0
THEN LOCATE( '&', url_real, LOCATE( 'link_id=', url_real, 0 ) +9 ) - LOCATE( 'link_id=', url_real ) +6
ELSE LENGTH( url_real ) - LOCATE( 'link_id=', url_real ) +7
END )
)
FROM health3_acesef_urls WHERE url_real LIKE "%index.php?option=com_mtree&link_id=84485&task=viewlink%"

Open in new window

0
 

Author Comment

by:jwleys
ID: 39183060
Thank you so much for your time and input, it is much appreciated :)

Also tried which returns 20,937 blank results:

SELECT SUBSTR( url_real, LOCATE( 'link_id=', url_real ) +8, (

CASE WHEN LOCATE( '&', url_real, LOCATE( 'link_id=', url_real ) +9 ) >0
THEN LOCATE( '&', url_real, LOCATE( 'link_id=', url_real, 0 ) +9 ) - LOCATE( 'link_id=', url_real ) +6
ELSE LENGTH( url_real ) - LOCATE( 'link_id=', url_real ) +7
END )
)
FROM health3_acesef_urls WHERE url_real LIKE "%link_id=%"

Open in new window

0
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
LVL 110

Expert Comment

by:Ray Paseur
ID: 39183064
What scripting language are you using?  For example, in PHP, the $_GET array contains the request variables.  If you use the request variables you can make the query much easier!
0
 
LVL 25

Expert Comment

by:chaau
ID: 39183099
can you add url_real to the select statement above and show us example of strings that won't work?
0
 

Author Comment

by:jwleys
ID: 39183104
I am using PHP, however, in this particular instance I am trying to run a script in MySQL to find all records containing a link_id as a sub-string and then store that link_id e.g. 84485 to another column in the same record so that I can identify a relationship between Mosets Tree and AceSEF.

By all means if you can think of a way to do this in PHP I am all ears, however, we also need a lot of records to update and consider.

Bear in mind there is no relationship between these two tables other than the fact that the link_id may be a sub string of the real url.
0
 
LVL 25

Expert Comment

by:chaau
ID: 39183112
On a side note, you can use REGEXP function, which is very powerful. Unfortunately I am not an expert in these crazy regexp statements, but I am sure there are plenty of experts here who knows
0
 

Author Comment

by:jwleys
ID: 39183129
Here is an example of a real_url which doesn't work, note that it doesn't contain a link_id:
index.php?option=com_content&catid=2&id=11&view=article

The sub-string returned by the query you provided in this case is:
hp?option=com_content&catid=2&id=11

Thank you so much for your input, you guys are really helping me out, cheers!! :)
0
 
LVL 25

Expert Comment

by:chaau
ID: 39183135
What do you want to extract if it does not contain link_id? This is what you have asked:

What I want to do is extract the value of link_id only which is 84485 so in theory my left side delimiter should be "link_id=" and my right side delimiter should be "&"
0
 

Author Comment

by:jwleys
ID: 39183146
If it does not contain a link_id I do not want to extract anything, I only want to consider and select from records that contain link_id.

The other challenge here is that the real_url will contain a string of variable length :S
0
 
LVL 25

Expert Comment

by:chaau
ID: 39183158
Just add WHERE LOCATE( 'link_id=', url_real ) > 0 to the SQL Select.

My solution works for variable length, as well as for the case where the link_id is the last parameter in the url
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39183199
select
  id
, url_real
, @pos1 := IFNULL( LOCATE('link_id=', url_real )   ,0) pos1
, @pos2 := IFNULL( LOCATE('&', url_real, @pos1+8 ) ,0) pos2
, case when @pos1 > 0 and @pos2=0   then SUBSTR(url_real,@pos1+8)
       when @pos1 > 0 and @pos2 > 0 then SUBSTR(url_real,@pos1+8, @pos2 - (@pos1+8))
  end link_id
from health3_acesef_urls
cross join (select @pos1 :=0, @pos2 :=0) posns

Open in new window

see: http://sqlfiddle.com/#!9/f5d37/1
0
 

Author Comment

by:jwleys
ID: 39183222
Unfortunately returns a whole bunch of blank results
Example SQL result with real_url selected as well
0
 

Author Comment

by:jwleys
ID: 39183230
Example real_url that doesn't work:
index.php?option=com_mtree&Itemid=&link_id=316717&task=viewlink

Correct me if I am wrong but would I just need to add an additional condition to the case statement to account for the variable string length that the real_url could be?

Once again thank you so much for your help :)
0
 
LVL 49

Accepted Solution

by:
PortletPaul earned 250 total points
ID: 39183231
or, a more parameter driven approach (with required where clause: http://sqlfiddle.com/#!9/d3c04/1 
select @look4 := 'link_id=';
select @lenof := length(@look4);

select
  id
, url_real
, @pos1 := IFNULL( LOCATE(@look4, url_real )              ,0) pos1
, @pos2 := IFNULL( LOCATE('&', url_real, @pos1 + @lenof ) ,0) pos2
, case when @pos1 > 0 and @pos2 = 0 then SUBSTR(url_real,@pos1+@lenof)
       when @pos1 > 0 and @pos2 > 0 then SUBSTR(url_real,@pos1+@lenof, @pos2 - (@pos1+@lenof))
  end link_id
from health3_acesef_urls
cross join (select @pos1 :=0, @pos2 :=0) posns
where LOCATE(@look4, url_real ) > 0

Open in new window

0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39183234
>>index.php?option=com_mtree&Itemid=&link_id=316717&task=viewlink
see http://sqlfiddle.com/#!9/c3df2/1

that test case works with this approach
0
 

Author Closing Comment

by:jwleys
ID: 39183264
Thank you PortletPaul that works perfectly for all scenarios, although your answer is the one I will be using I also want to award 50% of the points to chaau as he really stuck in there with me and helped me troubleshoot this and get a greater understanding of SQL. I also believe his comments would have helped you in the construction of your answer.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39183297
sharing of points is utterly fair, we appreciate it when effort is duly rewarded
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 39184120
Going forward, please create the SSCCE and post the test data here, along with the desired outcome.  It need not be a large amount of test data - just enough to illustrate the input and output that you need.  Computer programming is only about one thing -- transforming data from one form to another form.  When you work with a test-driven mindset you get accurate results much faster!

Best regards, ~Ray
0
 

Author Comment

by:jwleys
ID: 39185925
Hi Ray_Paseur, thanks for your comment I will endeavor  to create a SSCCE and post test data here, along with the desired outcome in the future.

Thank you for your help :)
0

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question