INSTR(string, substring [, position [, occurrence]])
-- position > 0: 從左向右,起始位置
-- position < 0: 從右向左,起始位置(倒數)
-- occurrence: 第 N 次出現,默認 1
MySQL INSTR(str, substr) 僅支持前兩個參數,**不支持指定起始位置、出現次數、反向搜索**,遷移 Oracle 應用時須自行實現。
DELIMITER $$
DROP FUNCTION IF EXISTS `func_instr_oracle`$$
CREATE FUNCTION `func_instr_oracle`(
f_str VARCHAR(1000), -- 源字符串
f_substr VARCHAR(100), -- 子串
f_str_pos INT, -- 起始位置(正:左→右;負:右→左)
f_count INT UNSIGNED -- 第 N 次出現
) RETURNS INT UNSIGNED
DETERMINISTIC
BEGIN
DECLARE i INT DEFAULT 0; -- 位置迭代器
DECLARE j INT DEFAULT 0; -- 匹配計數
DECLARE v_substr_len INT UNSIGNED DEFAULT 0;
DECLARE v_str_len INT UNSIGNED DEFAULT 0;
SET v_str_len = LENGTH(f_str);
SET v_substr_len = LENGTH(f_substr);
IF f_str_pos > 0 THEN -- 正向搜索
SET i = f_str_pos;
SET j = 0;
WHILE i <= v_str_len DO
IF INSTR(LEFT(SUBSTR(f_str, i), v_substr_len), f_substr) > 0 THEN
SET j = j + 1;
IF j = f_count THEN RETURN i; END IF;
END IF;
SET i = i + 1;
END WHILE;
ELSEIF f_str_pos < 0 THEN -- 反向搜索
SET i = v_str_len + f_str_pos + 1; -- 負數轉正向索引
SET j = 0;
WHILE i <= v_str_len AND i > 0 DO
IF INSTR(RIGHT(SUBSTR(f_str, 1, i), v_substr_len), f_substr) > 0 THEN
SET j = j + 1;
IF j = f_count THEN RETURN i - v_substr_len + 1; END IF;
END IF;
SET i = i - 1;
END WHILE;
ELSE -- position = 0 返回 0
RETURN 0;
END IF;
RETURN 0; -- 未找到
END$$
DELIMITER ;
| 調用 | 預期 | 實際 | 說明 |
|---|---|---|---|
func_instr_oracle('This is belong to you, but not to me.', 'to', 1, 1) | 16 | 16 | 正向第 1 次 |
func_instr_oracle('This is belong to you, but not to me.', 'to', 1, 2) | 32 | 32 | 正向第 2 次 |
func_instr_oracle('This is belong to you, but not to me.', 'belong', -1, 1) | 9 | 9 | 反向第 1 次(從右往左) |
func_instr_oracle('This is belong to you, but not to me.', 'belong', -1, 2) | 0 | 0 | 反向第 2 次不存在 |
CREATE FUNCTION `INSTR2`(
v_string VARCHAR(5000),
v_delimiter VARCHAR(20),
pos INT,
nth INT
) RETURNS VARCHAR(5000)
BEGIN
DECLARE icount INT DEFAULT 0;
DECLARE len INT DEFAULT 0;
DECLARE len1 INT DEFAULT 0;
DECLARE str1 VARCHAR(5000) DEFAULT '';
SET len = LENGTH(v_string);
SET len1 = LENGTH(v_delimiter);
SET icount = INSTR(v_string, v_delimiter);
IF icount = 0 THEN RETURN 0; END IF;
IF pos IS NULL THEN RETURN icount;
ELSEIF pos < 0 THEN RETURN LOCATE(v_delimiter, v_string, len + pos - 1);
ELSEIF pos = 0 THEN RETURN 0;
ELSEIF pos = 1 THEN
IF nth IS NULL THEN RETURN icount;
ELSEIF nth >= 1 THEN
SET icount = IF(LENGTH(SUBSTRING_INDEX(v_string, v_delimiter, nth)) = 0
OR LENGTH(SUBSTRING_INDEX(v_string, v_delimiter, nth)) = len,
0,
LENGTH(SUBSTRING_INDEX(v_string, v_delimiter, nth)) + 1);
ELSE RETURN 0; END IF;
ELSE
IF nth IS NULL OR nth = 1 THEN RETURN LOCATE(v_delimiter, v_string, pos);
ELSEIF nth > 1 THEN
SET str1 = SUBSTRING(v_string, pos);
SET icount = IF(LENGTH(SUBSTRING_INDEX(str1, v_delimiter, nth)) = 0
OR LENGTH(SUBSTRING_INDEX(str1, v_delimiter, nth)) = LENGTH(str1),
0,
LENGTH(SUBSTRING_INDEX(str1, v_delimiter, nth)));
IF icount <> 0 THEN SET icount = icount + pos; END IF;
RETURN icount;
END IF;
END IF;
RETURN 0;
END;
注意:INSTR2 針對分隔符場景優化,通用性不如 func_instr_oracle。
DETERMINISTIC 允許優化器緩存結果