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

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

Jeremy LeysTechnical LeadAsked:
Who is Participating?
 
Tomas Helgi JohannssonCommented:
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
 
ManishLeadCommented:
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
 
Jeremy LeysTechnical LeadAuthor Commented:
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
SharathData EngineerCommented:
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
 
Jeremy LeysTechnical LeadAuthor Commented:
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
 
Tomas Helgi JohannssonCommented:
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
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.

All Courses

From novice to tech pro — start learning today.