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

How do I select text between two delimiters in MySQL?

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
Jeremy Leys
Asked:
Jeremy Leys
  • 9
  • 5
  • 4
  • +1
2 Solutions
 
chaauCommented:
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
 
Jeremy LeysTechnical LeadAuthor Commented:
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
 
Jeremy LeysTechnical LeadAuthor Commented:
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
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
Ray PaseurCommented:
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
 
chaauCommented:
can you add url_real to the select statement above and show us example of strings that won't work?
0
 
Jeremy LeysTechnical LeadAuthor Commented:
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
 
chaauCommented:
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
 
Jeremy LeysTechnical LeadAuthor Commented:
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
 
chaauCommented:
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
 
Jeremy LeysTechnical LeadAuthor Commented:
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
 
chaauCommented:
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
 
PortletPaulfreelancerCommented:
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
 
Jeremy LeysTechnical LeadAuthor Commented:
Unfortunately returns a whole bunch of blank results
Example SQL result with real_url selected as well
0
 
Jeremy LeysTechnical LeadAuthor Commented:
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
 
PortletPaulfreelancerCommented:
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
 
PortletPaulfreelancerCommented:
>>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
 
Jeremy LeysTechnical LeadAuthor Commented:
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
 
PortletPaulfreelancerCommented:
sharing of points is utterly fair, we appreciate it when effort is duly rewarded
0
 
Ray PaseurCommented:
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
 
Jeremy LeysTechnical LeadAuthor Commented:
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
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.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 9
  • 5
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now