闲的无聊写了个仿MySQL replaceall() 函数

 

 

BEGIN

-- 用于控制循环是否结束 DECLARE DONE INT DEFAULT -1;

-- 变量声明 DECLARE PARAM_OUT VARCHAR(512) DEFAULT ''; DECLARE VAR_STR VARCHAR(512) DEFAULT ''; DECLARE VAR_STR_NEW VARCHAR(512) DEFAULT ''; DECLARE VAR_SPLIT_STR VARCHAR(512) DEFAULT ''; DECLARE VAR_SPLIT_SYMBOL VARCHAR(512) DEFAULT '';

-- 记录上次字符串中某符号出现的位置 DECLARE VAR_LOG_SYMBOL_INDEX INTEGER(12) DEFAULT 0; -- 记录字符串截取开始、结束和字符出现的位置 DECLARE VAR_STR_INDEX_START INTEGER(12) DEFAULT 0; DECLARE VAR_STR_INDEX_END INTEGER(12) DEFAULT 0; DECLARE VAR_STR_SYMBOL_INDEX INTEGER(12) DEFAULT 0;

-- 记录每次更新后字符串截取开始、结束和字符出现的位置 DECLARE VAR_STR_NEW_INDEX_START INTEGER(12) DEFAULT 0; DECLARE VAR_STR_NEW_INDEX_END INTEGER(12) DEFAULT 0; DECLARE VAR_STR_NEW_SYMBOL_INDEX INTEGER(12) DEFAULT 0;

-- 设置字符出现在字符串中的首次位置 SET VAR_STR_SYMBOL_INDEX = INSTR(SPLIT_STR, SPLIT_SYMBOL);

-- 字符串存在该符号 则遍历数据 IF VAR_STR_SYMBOL_INDEX <> 0 THEN

-- 首次字符串截取位置初始化 SET PARAM_OUT = ''; SET VAR_STR_INDEX_START = 1; SET VAR_STR_INDEX_END = VAR_STR_SYMBOL_INDEX - 1; SET VAR_LOG_SYMBOL_INDEX = VAR_STR_SYMBOL_INDEX;

-- 循环操作 MYLOOP: LOOP -- 循环结束标志 IF DONE = 1 THEN LEAVE MYLOOP; END IF; -- SELECT VAR_STR_NEW, VAR_STR_NEW_SYMBOL_INDEX, VAR_STR_INDEX_START, VAR_STR_INDEX_END, VAR_LOG_SYMBOL_INDEX FROM DUAL; -- 每次截取的字符串 SELECT SUBSTR(SPLIT_STR, VAR_STR_INDEX_START, VAR_STR_INDEX_END) FROM DUAL INTO VAR_STR;

-- 每次截取字符串后产生新的字符串 SET VAR_STR_NEW = SUBSTR(SPLIT_STR, VAR_LOG_SYMBOL_INDEX + 1); SET VAR_STR_NEW_SYMBOL_INDEX = INSTR(VAR_STR_NEW, SPLIT_SYMBOL);

-- 拼接每次截取出来的字符串 IF LENGTH(PARAM_OUT) <> 0 THEN SET PARAM_OUT = CONCAT(PARAM_OUT, REPLACED_SYMBOL, VAR_STR); END IF;

-- 拼接每次截取出来的字符串 IF LENGTH(PARAM_OUT) = 0 THEN SET PARAM_OUT = CONCAT(PARAM_OUT, VAR_STR); END IF;

-- 下一次字符串截取位置的初始化 IF VAR_STR_NEW_SYMBOL_INDEX <> 0 THEN SET VAR_STR_INDEX_START = VAR_LOG_SYMBOL_INDEX + 1; SET VAR_LOG_SYMBOL_INDEX = VAR_LOG_SYMBOL_INDEX + VAR_STR_NEW_SYMBOL_INDEX; SET VAR_STR_INDEX_END = VAR_STR_NEW_SYMBOL_INDEX - 1; END IF;

-- 最后一次字符串不截取直接拼接并设置离开循环标志 IF VAR_STR_NEW_SYMBOL_INDEX = 0 AND LENGTH(VAR_STR_NEW) > 0 THEN SET PARAM_OUT = CONCAT(PARAM_OUT, REPLACED_SYMBOL, VAR_STR_NEW); -- SELECT VAR_STR_NEW, VAR_STR_NEW_SYMBOL_INDEX, VAR_STR_INDEX_START, VAR_STR_INDEX_END, VAR_LOG_SYMBOL_INDEX FROM DUAL; SET DONE = 1; END IF;

-- 设置离开循环标志 IF VAR_STR_NEW_SYMBOL_INDEX = 0 AND LENGTH(VAR_STR_NEW) < 1 THEN SET DONE = 1; END IF;

-- 关闭循环 END LOOP MYLOOP;

END IF; RETURN PARAM_OUT;END

转载于:https://www.cnblogs.com/Dream-Lasting/p/8991930.html