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

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

0
Jeremy Leys
Asked:
Jeremy Leys
1 Solution
 
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
 
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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
 
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

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now