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
291 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
ID: 39625343
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
ID: 39625436
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
ID: 39635331
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
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 25

Accepted Solution

by:
Tomas Helgi Johannsson earned 500 total points
ID: 39637558
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
ID: 39639666
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 25

Expert Comment

by:Tomas Helgi Johannsson
ID: 39639808
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Amazon Redshift 2 39
myqsl update statement on phpMyAdmin 8 36
Coldfusion/Mysql page error related to dynamic table creation. 9 33
check mysql insert 12 26
All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

856 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