?
Solved

How do I select text between two delimiters in MySQL?

Posted on 2013-05-20
20
Medium Priority
?
2,510 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 1000 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 111

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 1000 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 111

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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

Creating and Managing Databases with phpMyAdmin in cPanel.
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

719 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