Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2591
  • 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
jwleys
Asked:
jwleys
  • 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
 
jwleysAuthor 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
 
jwleysAuthor 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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
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
 
jwleysAuthor 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
 
jwleysAuthor 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
 
jwleysAuthor 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
 
PortletPaulCommented:
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
 
jwleysAuthor Commented:
Unfortunately returns a whole bunch of blank results
Example SQL result with real_url selected as well
0
 
jwleysAuthor 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
 
PortletPaulCommented:
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
 
PortletPaulCommented:
>>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
 
jwleysAuthor 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
 
PortletPaulCommented:
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
 
jwleysAuthor 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

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

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