问题:
比如有个字段tag存储了多个预先设置的标签,现在要找出与输入匹配的记录,输入的字符串为多个空格分割的字符串组成
比如:输入字符串为 “HP 惠普 2008 笔记本”,要求能找出tag字段中所有包含 HP 或 笔记本 。。。。的记录
就是类似于简单的搜索引擎实现
SELECT tag
FROM product
WHERE REGEXP_LIKE (tag,
REPLACE (UPPER (fn_compress_space (inputstr)), ' ',
's*|')
);
FROM product
WHERE REGEXP_LIKE (tag,
REPLACE (UPPER (fn_compress_space (inputstr)), ' ',
's*|')
);
其中 fn_compress_space 是一个将多个连续空格压缩成单个空格的函数:
CREATE OR REPLACE FUNCTION fn_compress_space (p_str IN VARCHAR2)
/**//*
将一个字符串中的多个连续空格或tab符号格式化成单个半角空格的字符串
*/
RETURN VARCHAR2
IS
len INT := 0;
str1 VARCHAR2 (2000);
str2 VARCHAR2 (4);
str3 VARCHAR2 (4) := 'a';
strout VARCHAR2 (2000);
BEGIN
-- 把全角的空格和tab符、半角的tab符都转化成半角空格
str1 :=
REPLACE (REPLACE (REPLACE (p_str, ' ', ' '), ' ', ' '), ' ', ' ');
len := LENGTH (str1);
FOR i IN 1 .. len
LOOP
str2 := SUBSTR (str1, i, 1);
IF str2 != ' '
THEN
strout := strout || str2;
ELSIF str3 != ' '
THEN
strout := strout || str2;
END IF;
str3 := str2;
END LOOP;
RETURN TRIM (strout);
END fn_compress_space;
/
/**//*
将一个字符串中的多个连续空格或tab符号格式化成单个半角空格的字符串
*/
RETURN VARCHAR2
IS
len INT := 0;
str1 VARCHAR2 (2000);
str2 VARCHAR2 (4);
str3 VARCHAR2 (4) := 'a';
strout VARCHAR2 (2000);
BEGIN
-- 把全角的空格和tab符、半角的tab符都转化成半角空格
str1 :=
REPLACE (REPLACE (REPLACE (p_str, ' ', ' '), ' ', ' '), ' ', ' ');
len := LENGTH (str1);
FOR i IN 1 .. len
LOOP
str2 := SUBSTR (str1, i, 1);
IF str2 != ' '
THEN
strout := strout || str2;
ELSIF str3 != ' '
THEN
strout := strout || str2;
END IF;
str3 := str2;
END LOOP;
RETURN TRIM (strout);
END fn_compress_space;
/
以上利用 Oracle 10g 的正则函数实现,但10g前并没有提供正则函数,那么只能使用 like 了:
CREATE OR REPLACE FUNCTION fn_compress_space1 (p_str IN VARCHAR2)
/**//*
将一个字符串中的多个连续空格或tab符号输出为字符串数组
*/
RETURN ty_str_split
IS
len INT := 0;
str1 VARCHAR2 (2000);
str2 VARCHAR2 (4);
strout VARCHAR2 (2000);
str_split ty_str_split := ty_str_split ();
BEGIN
-- 把全角的空格和tab符、半角的tab符都转化成半角空格
str1 :=
UPPER (REPLACE (REPLACE (REPLACE (TRIM (p_str), ' ', ' '), ' ', ' '),
' ',
' '
)
);
len := LENGTH (str1);
FOR i IN 1 .. len
LOOP
str2 := SUBSTR (str1, i, 1);
IF str2 != ' '
THEN
strout := strout || str2;
ELSIF strout IS NOT NULL
THEN
str_split.EXTEND;
str_split (str_split.COUNT) := strout;
strout := '';
END IF;
END LOOP;
str_split.EXTEND;
str_split (str_split.COUNT) := strout;
RETURN str_split;
END fn_compress_space1;
/
/**//*
将一个字符串中的多个连续空格或tab符号输出为字符串数组
*/
RETURN ty_str_split
IS
len INT := 0;
str1 VARCHAR2 (2000);
str2 VARCHAR2 (4);
strout VARCHAR2 (2000);
str_split ty_str_split := ty_str_split ();
BEGIN
-- 把全角的空格和tab符、半角的tab符都转化成半角空格
str1 :=
UPPER (REPLACE (REPLACE (REPLACE (TRIM (p_str), ' ', ' '), ' ', ' '),
' ',
' '
)
);
len := LENGTH (str1);
FOR i IN 1 .. len
LOOP
str2 := SUBSTR (str1, i, 1);
IF str2 != ' '
THEN
strout := strout || str2;
ELSIF strout IS NOT NULL
THEN
str_split.EXTEND;
str_split (str_split.COUNT) := strout;
strout := '';
END IF;
END LOOP;
str_split.EXTEND;
str_split (str_split.COUNT) := strout;
RETURN str_split;
END fn_compress_space1;
/
将多个关键字放到一个字符数组中,查询时需要去重:
SELECT DISTINCT tag
FROM product a,
TABLE (CAST (fn_compress_space1 (inputstr) AS ty_str_split)) b
WHERE a.tag LIKE '%' || b.COLUMN_VALUE || '%';
FROM product a,
TABLE (CAST (fn_compress_space1 (inputstr) AS ty_str_split)) b
WHERE a.tag LIKE '%' || b.COLUMN_VALUE || '%';