问题[pattern-matching](dba)

我正在尝试在我的网站上设置搜索功能。我有一个名为项目的表，其中包含以下内容：

+----+--------+------------------------------------------------------------+--------------+--------+----------+---------+------------+
| id | name   | description                                                | creator      | visits | file     | version | date       |
+----+--------+------------------------------------------------------------+--------------+--------+----------+---------+------------+
| 16 | test 1 | <h1>This is a test thing.</h1>
<h3>The file is blank.</h3> | warriorjacq9 |      1 | imported |       0 | 2022-09-23 |
+----+--------+------------------------------------------------------------+--------------+--------+----------+---------+------------+

当我运行这个 sql 查询时：

SELECT * FROM projects WHERE MATCH(name, creator) AGAINST ('*warr*');

它返回一个空集。但是，当我将“warr”更改为“warriorjacq9”时，它会返回该行。我想知道为什么会发生这种情况，如果有不同的方式我应该这样做以使其按我想要的方式工作。

我有一个表，products其中有一product_id列如下：

product_id
110177
110177T
177

我正在尝试过滤应该获取如下结果的产品结果，但它也在获取 id177

110177
110177T

询问 -select * from products where produuct_id like %'177'%

查询中的哪些更新以丢弃177搜索结果中的字符串并仅获取其余两个？

我想知道是否有比以下更好的解决方案来构造一个新列（place_type_new），方法是place_type根据第三列的值（inhabitant在此处显示的案例）：

  UPDATE places_table SET place_type_new = CASE
      WHEN inhabitant = 0 AND place_type LIKE 'Village' THEN 'Village_XXXS'
      WHEN inhabitant = 10 AND place_type LIKE 'Village' THEN 'Village_XXS'
      WHEN inhabitant = 20 AND place_type LIKE 'Village' THEN 'Village_XS'
      WHEN inhabitant = 100 AND place_type LIKE 'Village' THEN 'Village_S'
      WHEN inhabitant = 2000 AND place_type LIKE 'Village' THEN 'Village_M'
      WHEN inhabitant = 5000 AND place_type LIKE 'Village' THEN 'Village_L'
      WHEN inhabitant = 10000 AND place_type LIKE 'Village' THEN 'Village_XL'
      WHEN inhabitant = 20000 AND place_type LIKE 'Village' THEN 'Village_XXL'
      WHEN inhabitant = 50000 AND place_type LIKE 'Village' THEN 'Village_XXXL'
      WHEN inhabitant = 0 AND place_type LIKE 'VillagePart' THEN 'VillagePart_XXXS'
      WHEN inhabitant = 10 AND place_type LIKE 'VillagePart' THEN 'VillagePart_XXS'
      WHEN inhabitant = 20 AND place_type LIKE 'VillagePart' THEN 'VillagePart_XS'
      WHEN inhabitant = 100 AND place_type LIKE 'VillagePart' THEN 'VillagePart_S'
      WHEN inhabitant = 2000 AND place_type LIKE 'VillagePart' THEN 'VillagePart_M'
      WHEN inhabitant = 5000 AND place_type LIKE 'VillagePart' THEN 'VillagePart_L'
      WHEN inhabitant = 10000 AND place_type LIKE 'VillagePart' THEN 'VillagePart_XL'
      WHEN inhabitant = 20000 AND place_type LIKE 'VillagePart' THEN 'VillagePart_XXL'
      WHEN inhabitant = 50000 AND place_type LIKE 'VillagePart' THEN 'VillagePart_XXXL'
      WHEN inhabitant = 0 AND place_type LIKE 'Neighborhood' THEN 'Neighborhood_XXXS'
      WHEN inhabitant = 10 AND place_type LIKE 'Neighborhood' THEN 'Neighborhood_XXS'
      WHEN inhabitant = 20 AND place_type LIKE 'Neighborhood' THEN 'Neighborhood_XS'
      WHEN inhabitant = 100 AND place_type LIKE 'Neighborhood' THEN 'Neighborhood_S'
      WHEN inhabitant = 2000 AND place_type LIKE 'Neighborhood' THEN 'Neighborhood_M'
      WHEN inhabitant = 5000 AND place_type LIKE 'Neighborhood' THEN 'Neighborhood_L'
      WHEN inhabitant = 10000 AND place_type LIKE 'Neighborhood' THEN 'Neighborhood_XL'
      WHEN inhabitant = 20000 AND place_type LIKE 'Neighborhood' THEN 'Neighborhood_XXL'
      WHEN inhabitant = 50000 AND place_type LIKE 'Neighborhood' THEN 'Neighborhood_XXXL'
      WHEN inhabitant = 0 AND place_type LIKE 'NeighborhoodPart' THEN 'NeighborhoodPart_XXXS'
      WHEN inhabitant = 10 AND place_type LIKE 'NeighborhoodPart' THEN 'NeighborhoodPart_XXS'
      WHEN inhabitant = 20 AND place_type LIKE 'NeighborhoodPart' THEN 'NeighborhoodPart_XS'
      WHEN inhabitant = 100 AND place_type LIKE 'NeighborhoodPart' THEN 'NeighborhoodPart_S'
      WHEN inhabitant = 2000 AND place_type LIKE 'NeighborhoodPart' THEN 'NeighborhoodPart_M'
      WHEN inhabitant = 5000 AND place_type LIKE 'NeighborhoodPart' THEN 'NeighborhoodPart_L'
      WHEN inhabitant = 10000 AND place_type LIKE 'NeighborhoodPart' THEN 'NeighborhoodPart_XL'
      WHEN inhabitant = 20000 AND place_type LIKE 'NeighborhoodPart' THEN 'NeighborhoodPart_XXL'
      WHEN inhabitant = 50000 AND place_type LIKE 'NeighborhoodPart' THEN 'NeighborhoodPart_XXXL'
      ELSE place_type
  END;

这里Village,VillagePart和是更大的一组地方的一部分（例如还有,Neighborhood等等），我只希望这条规则适用于这 4 个元素，因此我的陈述无处不在。NeighborhoodPartTownCityLIKE

但我觉得这非常丑陋，但它工作得很好。附加到现有字段值places_table以构建新字段值的后缀始终根据此模式place_table_new匹配列中的相同数字：inhabitant

0     : XXXS
10    :  XXS
20    :   XS
100   :    S
2000  :    M
5000  :    L
10000 :   XL
20000 :  XXL
50000 : XXXL

为了实现这一点，用这个映射构建一个中间表会更好吗？

我在 Ubuntu 18.04 上使用 PostgreSQL 12。我还将这里的问题缩小到尽可能简单的范围内，希望不会忘记一些重要的事情。

回到 SQL Server，我使用下面的命令#1234从名为的列中获取匹配的字符串模式Number

SELECT [m].[Number]
FROM [Boats] AS [m]
WHERE [m].[Number] LIKE N'[#][0-9][0-9][0-9][0-9]'

现在在 Postgres 中，我尝试使用以下命令，但没有运气：

SELECT m.Number
FROM Boats AS m
WHERE m.Number ILIKE '#[0-9][0-9][0-9][0-9]'

知道我应该使用什么模式表达式来获得正确的过滤器吗？

我的表中有一个邮政编码列，其中包含,AB45*等值。RD4*ADB567

现在我想将我的数据与此列匹配。这*意味着以 开头的字符串AB45应与此列匹配。如果我使用AB45 45D它应该获取包含的行AB45*

如果我RD489在我的字段上使用，它应该与RD4*列值匹配。

我可以用什么来实现这个要求？

给定一个事件的 GUID，比如登录，我如何利用其他列数据搜索相似或不同的事件？

例如：查找与 [GUID] {Server:"s123", State="NY", Status="503", etc...} 类似的登录信息

或者

查找与给出的示例相反的登录名？

我觉得必须有一个名称，但我认为全文或“%Like%”语法是不够的。

使用 solr 或 elasticsearch 索引数据是解决方案吗？

在 MS-Access 中，我可以在 where 子句中使用字符范围/集：

SELECT
    table_name
FROM 
    all_tab_columns
WHERE
        table_name NOT LIKE 'A[0-9]*' 
    AND table_name NOT LIKE 'D[0-9]*' 
    AND table_name NOT LIKE 'S[0-9]*'

字符范围/集合是0-9之间的数字范围。从查询中删除所有以 A、D 或 S 开头，后跟数字的表。

我如何在 Oracle 中执行此操作？

我有两个表：t1和t2。分为三个步骤：

1. 在t1select a set_of_stringswith id = 1 ("1a" and"3c")。
2. 从中t1选择对应于OPTIONID ='A'并包含子字符串的字符串set_of_strings。

t1

身份名称   
1个“1a”   
2“2b”   
1 "3c"   

t2

选项ID TXT   
  一个“7h 9t”   
  B“1a 8n”   
  一个“2b 4r”    
  一个“3c 6u”

查询

SELECT 
    NAME 
FROM t1 
WHERE ID = 1; -- 1a, 3c

SELECT
    TXT 
FROM t2 
WHERE OPTIONID = 'A' AND (TXT LIKE '%1a%' OR TXT LIKE '%3c%') --"3c 6u"

问题

如何使TXT LIKE '%1a%' OR TXT LIKE '%3c%'零件自动化？

LIKE'%1a%'是安全的，因为所有条目都具有相同的格式，即没有1a2inTXT列。

所以我们有一个名为 TEMPLATES 的表，它将 html 电子邮件模板存储为纯文本（不要问，做出该决定时我不在场）。该表中有许多列，但我们关心的是存储文本的 TEMPLATE 字段。

现在我们最近更新了服务器用来在将这些模板发送给用户之前解析这些模板的库。这些库使用新的变量语法

${varName}

我的问题是很多旧电子邮件模板使用旧语法

@varName@

这些变量名称仅包含字母数字字符。我正在尝试使用 REGEXP_REPLACE 替换 with 的所有实例，@whateverVariable@但是${whateverVariable}在进行替换时我很难保留变量名。这可能吗？有一个更好的方法吗？

编辑：如果有所不同，我可能应该提到该字段是一个 CLOB。

总而言之，今天我在处理充满词汇的 MySQL 表时遇到了一些麻烦。我需要能够快速检索具有相同辅音和元音排列的单词，所以我的想法是按照这些大约 30 万行的结构进行构建：

CREATE TABLE words(
 `value` VARCHAR(100),
 `pattern` VARCHAR(100)
);

填充一小部分内容：

INSERT INTO words(`value`) VALUES
  ('aardvark'),
  ('aardvarks'),
  ('aardwolf'),
  ('aardwolves'),
  #...
  ('mortal'),
  ('mortals'),
  #...
  ('posted'),
  ('posteen'),
  #...
  ('zymotoxic'),
  ('zymurgies');

以下查询应返回“mortal”和“posted”：

SELECT `value` FROM words WHERE `pattern` = '101101';

因为'1'代表辅音而'0'代表元音。我知道 MySQL 没有 regexp_replace 等效项，所以我想知道填充该pattern字段的最佳方法是什么？我假设它不是慢得令人遗憾：

UPDATE words SET `pattern` = REPLACE(`value`, 'a', '0');
UPDATE words SET `pattern` = REPLACE(`pattern`, 'b', '1');
UPDATE words SET `pattern` = REPLACE(`pattern`, 'c', '1');
UPDATE words SET `pattern` = REPLACE(`pattern`, 'd', '1');
UPDATE words SET `pattern` = REPLACE(`pattern`, 'e', '0');
#...
UPDATE words SET `pattern` = REPLACE(`pattern`, 'z', '1');

目前正在尝试使用游标循环遍历单词表并逐个更新每个模式的存储过程，但是，也许不足为奇，这并不快。我错过了一些非常明显的东西吗？

提前感谢您能给我的任何帮助。

编辑：添加了存储过程方法。这速度更快但幅度不大（每千行 0.7 秒，累加起来）但它完成了工作。

DELIMITER $$

CREATE DEFINER=`root`@`%` PROCEDURE `set_word_pattern`()
BEGIN
  DECLARE l_last_row INT DEFAULT 0;
  DECLARE temp_word VARCHAR(100);
  #DECLARE temp_char CHAR(1);
  DECLARE temp_pattern_word VARCHAR(100);

  DECLARE c_traverse_words CURSOR FOR SELECT `value` FROM words WHERE pattern = "" LIMIT 20000;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET l_last_row = 1;

  OPEN c_traverse_words;
  cursor_loop: LOOP
    FETCH c_traverse_words INTO temp_word;
    IF l_last_row = 1 THEN
      LEAVE cursor_loop;
    END IF;
    SET temp_pattern_word = get_pattern(temp_word);
    UPDATE words SET `pattern` = temp_pattern_word WHERE `value` = temp_word;
  END LOOP cursor_loop;
  CLOSE c_traverse_words;

  SELECT "Done"; #temp_pattern_word;
END

以及它调用的函数：

DELIMITER $$

CREATE DEFINER=`root`@`%` FUNCTION `get_pattern`(new_word VARCHAR(100)) RETURNS varchar(100) CHARSET utf8
BEGIN
  DECLARE temp_char CHAR(1);
  DECLARE temp_pattern_word VARCHAR(100);

  DECLARE i INT;

  SET temp_pattern_word = "";
  SET i = 1;

  WHILE(i <= CHAR_LENGTH(new_word)) DO
    SET temp_char = MID(new_word, i, 1);

    IF temp_char IN ("a", "e", "i", "o", "u") THEN
      SET temp_pattern_word = CONCAT(temp_pattern_word, "0");
    ELSE
      SET temp_pattern_word = CONCAT(temp_pattern_word, "1");
    END IF;
    SET i = i + 1;
  END WHILE;
RETURN temp_pattern_word;
END