代码之家 › 专栏 › 技术社区 › pouya

当单词不在[square]、(round)或{curly}大括号内时,从表列中选择该单词

sqlite sql

0

pouya · 技术社区 · 7 年前

使用 Like 我可以编写以下SQL查询来选择行,仅当 input 列有 help 单词。

select * 
from entry
where input like '% help %'
   or input like 'help %'
   or input like '% help'

如何将此SQL查询更改为选择行 帮助 不在里面 [] 或 () 或 {} .

样本数据:

+-------------------------------------------------------------+
|                            input                            |
+-------------------------------------------------------------+
| this is help { some help }                                  |
| help                                                        |
| another help [ help ]                                       |
| some text ( some words surrounding help. just for example ) |
| [ see help ] some text                                      |
| this is a sentence. { help }                                |
+-------------------------------------------------------------+

预期结果:

+----------------------------+
|           result           |
+----------------------------+
| this is help { some help } |
| help                       |
| another help [ help ]      |
+----------------------------+

3 回复 | 直到 7 年前

1

2

forpas 7 年前

我想到了这个:

select * 
from entry
where  
    trim(input) = 'help' or
    input like 'help %' or 
    input like '% help' or
    replace(replace(replace(replace(input, '[', '('), '{', '('), ']', ')'), '}', ')') like '% help %(%)%' or 
    replace(replace(replace(replace(input, '[', '('), '{', '('), ']', ')'), '}', ')') like '%(%)% help %' or
    (input like '% help %' AND instr(replace(replace(replace(replace(input, '[', '('), '{', '('), ']', ')'), '}', ')'), '(') = 0)

如果只有一对 () , [] 或 {} 在里面 input .

2

1

Ilyes 7 年前

试试这个

WITH entry AS
(
  SELECT 'this is help { some help }' input
  UNION
  SELECT 'help'
  UNION
  SELECT 'another help [ help ]'
  UNION  
  SELECT 'some text ( some words surrounding help . just for example )'
  UNION
  SELECT '[ see help ] some text'
  UNION
  SELECT 'this is a sentence. { help }' 
)
SELECT input
FROM
(
    SELECT input,
           CASE WHEN InStr(input, '[') > 0 THEN
                     InStr(input, '[')
                ELSE
                (CASE WHEN InStr(input, '(') > 0 THEN
                           InStr(input, '(')
                      ELSE InStr(input, '{')
                 END)
           END Idx,
           InStr(input, 'help') IdxH
   FROM entry
)
WHERE (Idx = 0 OR Idx > IdxH);

既然,如果 help 继后 ], ), } 然后

WITH entry AS
(
  SELECT 'this is help { some help }' input
  UNION
  SELECT 'help'
  UNION
  SELECT 'another help [ help ]'
  UNION  
  SELECT 'some text ( some words surrounding help . just for example )'
  UNION
  SELECT '[ see help ] some text'
  UNION
  SELECT 'this is a sentence. { help }' 
  UNION
  SELECT 'Asking for [help]. help me'
)
SELECT input
FROM
(
    SELECT input,
           CASE WHEN InStr(input, '[') > 0 THEN
                     InStr(input, '[')
                ELSE
                (CASE WHEN InStr(input, '(') > 0 THEN
                           InStr(input, '(')
                      ELSE InStr(input, '{')
                 END)
           END Idx,
           CASE WHEN InStr(input, ']') > 0 THEN
                     InStr(input, ']')
                ELSE
                (CASE WHEN InStr(input, ')') > 0 THEN
                           InStr(input, ')')
                      ELSE InStr(input, '}')
                 END)
           END Ix,
           InStr(input, 'help') IdxH
   FROM entry
)
WHERE (Idx = 0 OR Idx > IdxH OR InStr(SubStr(input, Ix), 'help') > 0)

返回:

+----------------------------+
|           input            |
+----------------------------+
| Asking for [help]. help me |
| another help [ help ]      |
| help                       |
| this is help { some help } |
+----------------------------+

Demo

3

0

Majva 7 年前

这是一个简单的解决方案:

SELECT * FROM entry
WHERE input LIKE 'help%'
OR input LIKE '%help'
OR input LIKE '%help%'
AND input NOT LIKE '%(%help%)%'
AND input NOT LIKE '%{%help%}%'
AND input NOT LIKE '%[%help%]%'

结果是 this