查看mysql当前是否支持编写自定义
SHOW variables like '%fun%';
log_bin_trust_function_creators=OFF
log_bin_trust_function_creators=ON
开启自定义函数
set global log_bin_trust_function_creators=1;
SELECT regexp_replace('abcdefg123456ABC','[a-z0-9]','&')
#创建前删除已经创建的自定义函数
drop function if exists regexp_replace;
#创建 regexp_replace函数
DELIMITER $$
CREATE FUNCTION `regexp_replace`(string_a VARCHAR(1000),pattern VARCHAR(1000),string_b VARCHAR(1000))
RETURNS VARCHAR(1000)
DETERMINISTIC
BEGIN
DECLARE string_c VARCHAR(1000);
DECLARE nub VARCHAR(1);
DECLARE i INT;
SET i =1;
SET string_c ='';
IF string_a REGEXP pattern THEN
loop_label: LOOP
IF i>CHAR_LENGTH(string_a) THEN
LEAVE loop_label;
END IF;
SET nub = SUBSTRING(string_a,i,1);
IF NOT nub REGEXP pattern THEN
SET string_c = CONCAT(string_c,nub);
ELSE
SET string_c = CONCAT(string_c,string_b);
END IF;
SET i=i+1;
END LOOP;
ELSE
SET string_c = string_a;
END IF;
RETURN string_c;
END$$
DELIMITER;
select t1.item_json,regexp_replace( t1.item_json,'[{""}]','') dd from t_reservation_check_single t1 where t1.id in (1251,1237);
-- JSON 示例:
{"GENERAL":["2257_ADC_CEX_LC","265_MAB_CE_NR","265_MAB_SEC_LC","ACTIVITY_LC","ADCC"]} GENERAL:[2257_ADC_CEX_LC,265_MAB_CE_NR,265_MAB_SEC_LC,ACTIVITY_LC,ADCC]
{"WBP2082":"2082_ELISA_L","WBP807":"807_ELISA_P"} WBP2082:2082_ELISA_L,WBP807:807_ELISA_P