Solved

How do I select a string that contains a sub-string but doesn't end with the same sub-string in MySQL?

Posted on 2013-11-05
6
286 Views
Last Modified: 2013-11-11
How do I select a string that contains a sub-string but doesn't end with the same sub-string
I was thinking something like this, but it doesn't work, it just ends up returning all results that contain the sub-string:
u.url_sef LIKE CONCAT( '%', c.alias, '%' )
AND u.url_sef NOT LIKE CONCAT( '%', c.alias )

SELECT b.url_sef
FROM health3_mt_cat_acesef_bridge AS b, 
health3_mt_cats AS c, 
health3_acesef_urls AS u, 
health3_acesef_metadata AS m, 
health3_acesef_sitemap AS s
WHERE b.id = u.id
AND u.url_sef = m.url_sef
AND u.url_sef = s.url_sef
AND (
s.published =0
OR m.published =0
)
AND u.url_sef LIKE CONCAT( '%', c.alias, '%' )
AND u.url_sef NOT LIKE CONCAT( '%', c.alias ) 

Open in new window

0
Comment
Question by:jwleys
6 Comments
 
LVL 11

Expert Comment

by:Manish
Comment Utility
Can you try with right function to check for not end with.
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_right
0
 

Author Comment

by:jwleys
Comment Utility
Thank you for your suggestion, I tried:

c.alias <> RIGHT( u.url_sef, LENGTH( c.alias ) )

Had the same result returned strings that contain the sub-string, didn't exclude strings that end with the sub-string. I think I am probably doing something wrong. Thank you for taking the time to help me :)

SELECT b.url_sef
FROM health3_mt_cat_acesef_bridge AS b, health3_mt_cats AS c, health3_acesef_urls AS u, health3_acesef_metadata AS m, health3_acesef_sitemap AS s
WHERE b.id = u.id
AND u.url_sef = m.url_sef
AND u.url_sef = s.url_sef
AND (
s.published =0
OR m.published =0
)
AND u.url_sef LIKE CONCAT( '%', c.alias, '%' )
AND c.alias <> RIGHT( u.url_sef, LENGTH( c.alias ) ) 

Open in new window

0
 
LVL 40

Expert Comment

by:Sharath
Comment Utility
I did not see any issue. can you post some sample data where it is not working.
Here is example where it is working.
http://sqlfiddle.com/#!9/6c15c/3
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 24

Accepted Solution

by:
Tomas Helgi Johannsson earned 500 total points
Comment Utility
Hi!

Line 14 in your example selects both the

This sql removes the records where  c.alias is at the end of the string in u.url_sef

SELECT b.url_sef
FROM health3_mt_cat_acesef_bridge AS b, 
health3_mt_cats AS c, 
health3_acesef_urls AS u, 
health3_acesef_metadata AS m, 
health3_acesef_sitemap AS s
WHERE b.id = u.id
AND u.url_sef = m.url_sef
AND u.url_sef = s.url_sef
AND (
s.published =0
OR m.published =0
)
AND u.url_sef LIKE CONCAT( '%', c.alias, '%' )
AND u.url_sef SUBSTRING(u.url_sef,LENGTH(u.url_sef)-LENGTH(c.alias)+1,LENGTH(u.url_sef)) <> c.alias

Open in new window


Hope this helps.

Regards,
    Tomas Helgi
0
 

Author Closing Comment

by:jwleys
Comment Utility
I ended up using the SQL below which worked for me, your logic was correct but contained a syntax error :

"AND u.url_sef SUBSTRING(u.url_sef,LENGTH(u.url_sef)-LENGTH(c.alias)+1,LENGTH(u.url_sef)) <> c.alias"

When removing "u.url_sef" from the AND clause the query worked perfectly,  thanks :)

SELECT DISTINCT b.url_sef
FROM health3_mt_cat_acesef_bridge AS b,
health3_mt_cats AS c,
health3_acesef_urls AS u,
health3_acesef_metadata AS m,
health3_acesef_sitemap AS s
WHERE b.id = u.id
AND u.url_sef = m.url_sef
AND u.url_sef = s.url_sef
AND (
s.published =0
OR m.published =0
)
AND u.url_sef LIKE CONCAT( '%', c.alias, '%' )
AND SUBSTRING(u.url_sef,LENGTH(u.url_sef)-LENGTH(c.alias)+1,LENGTH(u.url_sef)) <> c.alias
0
 
LVL 24

Expert Comment

by:Tomas Helgi Johannsson
Comment Utility
Hi!

Glad to help.
However a good cup of coffee  would have been nice before I wrote this. ;)
What I ment to say is : Line 14 in your example gives you  c.alias somewhere in the string including c.alias at the end of the string.
So the substring line I added to your query (and was meant to be as you corrected ) removes those cases.

Regards,
   Tomas Helgi
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

762 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

10 Experts available now in Live!

Get 1:1 Help Now