搜索文本字符串中的特定单词-SQL [英] Searching for specific words in a text string - SQL

问题描述

我想知道是否有人可以帮助我.我知道我做错了事，并使事情变得复杂了，但是我不确定是什么.

I wondered if someone might be able to help me. I know that I am doing something wrong and over-complicating this but i'm not sure what.

当有人使用自闭症，自闭症，阿斯伯格斯等单词回答问题时，我想从问题205,227和278中挑出文字.

I would like to pick out text from questions 205,227 and 278 when someone uses the words Autism, ASD, Aspergers etc... to answer the question.

发生的事情是我只得到一个单词短语，而不是有人将单词写在字符串中，例如输出中不会出现我有自闭症".仅当有人写自闭症时.我已经注意到它也是区分大小写的，我想知道是否可以对其进行编码，这样我就不必用大写和小写的所有组合来写所有单词.

What is happening is I am only getting single word phrases, rather than when someone writes the word in a string e.g. 'I have autism' will not appear in the output. Only when someone writes Autism. I have noticed it is case sensitive too and I wondered whether this can be coded so that I don't have to write all words in every combination of upper and lower case.

我需要的主要是特定问题(question.id)，并能够在这些问题的响应中搜索特定单词和短语的出现(texter_survey_response_value.value).

The main things I need are the specific questions (question.id) and to be able to search for the occurrence of specific words and phrases within the responses of these questions (texter_survey_response_value.value).

    select 
    texter_survey_response.response_id, question_id, conversation_id, 
    texter_survey_response_value.value as scrubbed_feedback
    from
    texter_survey_response
    join texter_survey_response_value on texter_survey_response.response_id = 
    texter_survey_response_value.response_id
    where
    texter_survey_response_value.question_id IN (205, 227, 278)
    and texter_survey_response_value.value = 'Autism'
    or texter_survey_response_value.value = 'ASD'
    or texter_survey_response_value.value = 'Autistic'
    or texter_survey_response_value.value = 'Aspergers'
    or texter_survey_response_value.value = 'Autism Spectrum'
    or texter_survey_response_value.value = 'Autistic Spectrum Disorder'
    or texter_survey_response_value.value = 'Autistic Spectrum'
    or texter_survey_response_value.value = 'Autism Spectrum Disorder'
    and texter_survey_response_value.value is not null

推荐答案

如果使用=，它将仅搜索完全匹配的内容.通过将%添加到搜索短语的两侧，可以使用like实现包含搜索.您可以通过仅使用大写或小写来解决大多数like实现的区分大小写的性质.

If you use =, it will only search for exact matches. You can achieve a contains-search with like by adding % to both sides of your search phrases. You can work around the case-sensitive nature of most like implementations by only working with upper or lower case.

select 
  texter_survey_response.response_id, question_id, conversation_id, 
  texter_survey_response_value.value as scrubbed_feedback
from
  texter_survey_response join texter_survey_response_value
    on texter_survey_response.response_id = texter_survey_response_value.response_id
where
  texter_survey_response_value.question_id IN (205, 227, 278)
  and texter_survey_response_value.value is not null
  and (
    UPPER(texter_survey_response_value.value) like '%AUTISM%' or
    UPPER(texter_survey_response_value.value) like '%AUTISTIC%' or
    UPPER(texter_survey_response_value.value) like '%ASPERGERS%' or
    UPPER(texter_survey_response_value.value) like '%ASD%');

您没有编写使用的数据库，因此您可能必须调用不同于UPPER的过程.

You didn't write what database you use, so you might have to call a different procedure than UPPER.

希望这会有所帮助:)

这篇关于搜索文本字符串中的特定单词-SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！