dsf23223
2018-03-12 16:24
浏览 124
已采纳

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

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.

图片转代码服务由CSDN问答提供 功能建议

tab_names(虚构示例)

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

在以下非常具体的情况下,'Fred'和'Fred - Family' 是同一个实体。

我需要从'Fred - Family'id中获取'Fred'id。

我知道这很奇怪......

我的想法要解决:

1-首先,我得到对应于id 8的'name'(即'Fred - Family') ;

2 - 然后我在数据库中查询与'Fred - Family'值相对应的行的id减去字符串' - Family'

插图:

  $ name_family ='Fred  -  Family'; 
 
SELECT name FROM tab_names WHERE name LIKE \“$ name_family \”
AND name NOT LIKE'  % - 家庭'
   
 
 

有关如何构建此查询的任何想法吗? 谢谢。

  • 写回答
  • 好问题 提建议
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • dqtm8504 2018-03-12 16:44
    已采纳

    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

    已采纳该答案
    评论
    解决 无用
    打赏 举报
  • dongzhong2008 2018-03-12 16:39

    If I follow your pattern, you can do something like this maybe :

    1/ Remove the '- Family' part :

    $name_family  = 'Fred - Family';
    $name_family = explode('-', $name_family);
    $name = trim($name_family[0]); //eg. Fred
    

    OR

    $name_family  = 'Fred - Family';
    $name = str_replace(' - Family', '' ,$name_family); //eg. Fred
    

    2/ Then you just search in your table by exact family name :

    SELECT name FROM tab_names WHERE name = \"$name\" 
    

    You should only get the entity where name is Fred (so 'Fred - Family' won't be return).

    Is this what you are looking for?

    评论
    解决 无用
    打赏 举报

相关推荐 更多相似问题