Solved

How do I select text between two delimiters in MySQL?

Posted on 2013-05-20
20
2,274 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
  • 9
  • 5
  • 4
  • +1
20 Comments
 
LVL 24

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
 
LVL 108

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 24

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 24

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 24

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
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 24

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 48

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 48

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 48

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 48

Expert Comment

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

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

867 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now