LIKE和NOT operatores - 没有预定单词的字符串的条件

Question

tab_names (fictional example)

id   name
3 - Fred
5 - Fred - Family
7 - Marie
9 - Marie - Family

In the following very specific situation, 'Fred' and 'Fred - Family' are the same entity.

I need to get 'Fred' id from 'Fred - Family' id.

I know it's strange ...

My idea to solve:

1- First I get the 'name' corresponding to id 8 (that is, 'Fred - Family');

2 - Then I query the database for the id of the row that corresponds to the value 'Fred - Family' minus the string '- Family'

Illustration:

$name_family  = 'Fred - Family';

SELECT name FROM tab_names WHERE name LIKE  \"$name_family\" 
AND name NOT LIKE  '% - Family'

Any ideas how to build this query? Thanks.

Answer 1

You could remove the '- Family' string directly in SQL.

SELECT * 
FROM tab_names 
WHERE 
name = (
   SELECT SUBSTRING(name, 1, LENGTH(name) - 9) 
   FROM tab_names 
   WHERE id = 8 LIMIT 1
)

Line SUBSTRING(name, 1, LENGTH(name) - 9) removes 9 characters from the end of the name