比如现在有一字符串:1,2,44,5,666,29232 要把它按照逗号分割成: 1 2 44 5 666 29232 而且还要求它的总数。这个我以前写过。不过今天规范一下。1、具体函数SQL code DELIMITER $$ CREATE DEFINER=`root`@`%` FUNCTION `func_get_split_string_total`( f_string varchar(1000),f_delimiter varchar(5) ) RETURNS int(11) BEGIN -- Get the total number of given string. return 1+(length(f_string) - length(replace(f_string,f_delimiter,''))); END$$ DELIMITER ; SQL code DELIMITER $$ CREATE DEFINER=`root`@`%` FUNCTION `func_get_split_string`( f_string varchar(1000),f_delimiter varchar(5),f_order int) RETURNS varchar(255) CHARSET utf8 BEGIN -- Get the separated number of given string. declare result varchar(255) default ''; set result = reverse(substring_index(reverse(substring_index(f_string,f_delimiter,f_order)),f_delimiter,1)); return result; END$$ DELIMITER ;测试的存储过程: DELIMITER $$ CREATE PROCEDURE `sp_print_result`( IN f_string varchar(1000),IN f_delimiter varchar(5) ) BEGIN -- Get the separated string. declare cnt int default 0; declare i int default 0; set cnt = func_get_split_string_total(f_string,f_delimiter); drop table if exists tmp_print; create temporary table tmp_print (num int not null); while i < cnt do set i = i + 1; insert into tmp_print(num) values (func_get_split_string(f_string,f_delimiter,i)); end while; select * from tmp_print;
END$$ DELIMITER ;2、来做一下测试 mysql> call sp_print_result('1,2,44,5,666,29232',','); +-------+ | num | +-------+ | 1 | | 2 | | 44 | | 5 | | 666 | | 29232 | +-------+ 6 rows in set (0.01 sec)Query OK, 0 rows affected (0.01 sec) windows 本地机器上成功测试 但是可信 linux 发现mysql 5.0.45 版本的等创建立函数不成功他不支持创建函数。 在不支持函数的mysql如何做这个字符处理??
需要辅助表LSB1字段ID,内容1-10000 SQL: SELECT NEWDD,LOCATE(',',CC,2) AS DF1, CAST(MID(CC,2,LOCATE(',',CC,2)-2) AS CHAR(100)) AS DF2 FROM ( SELECT A.*,B.ID AS ID1,MID(NEWDD,B.ID,LENGTH(NEWDD)) AS CC FROM ( SELECT CONCAT(',',favuser,',') AS NEWDD, id FROM ttH2) A INNER JOIN LSB1 B ON B.ID<=LENGTH(A.NEWDD) WHERE MID(MID(NEWDD,B.ID,LENGTH(NEWDD)),1,1)=',') A1 WHERE LENGTH(CC)>1 ORDER BY NEWDD;TTH2: 字段favuser,内容 10,12,13,15,52,3,4
ysql proc: CREATE PROCEDURE `MS_Data`.`P1` ( out a varchar(45), ) BEGIN declare b int default 0; select COUNT(*) into b from t_users; select b into a; END可以用存储过程那把前面的 函数给替换掉的
CREATE PROCEDURE `func_get_split_string_total`( f_string varchar(1000),f_delimiter varchar(5),out total int(11) ) BEGIN set total= 1+(length(f_string) - length(replace(f_string,f_delimiter,''))); END;CREATE PROCEDURE `func_get_split_string`( f_string varchar(1000),f_delimiter varchar(5),f_order int,out result varchar(255) ) BEGIN set result = reverse(substring_index(reverse(substring_index(f_string,f_delimiter,f_order)),f_delimiter,1)); END;CREATE PROCEDURE `sp_print_result`(IN f_string varchar(1000),IN f_delimiter varchar(5)) BEGIN declare cnt int default 0; declare i int default 0; declare total int;declare result varchar(255) default ''; call func_get_split_string_total(f_string,f_delimiter, total); drop table if exists tmp_print; create temporary table tmp_print (num int not null); while i < total do set i = i + 1; call func_get_split_string(f_string,f_delimiter,i,result); insert into tmp_print(num) values (result); end while; select * from tmp_print; END; 测试: call sp_print_result('1,2,44,5,666,29232',',');
要把它按照逗号分割成:
1
2
44
5
666
29232
而且还要求它的总数。这个我以前写过。不过今天规范一下。1、具体函数SQL code
DELIMITER $$ CREATE DEFINER=`root`@`%` FUNCTION `func_get_split_string_total`(
f_string varchar(1000),f_delimiter varchar(5)
) RETURNS int(11)
BEGIN -- Get the total number of given string.
return 1+(length(f_string) - length(replace(f_string,f_delimiter,'')));
END$$ DELIMITER ;
SQL code
DELIMITER $$
CREATE DEFINER=`root`@`%` FUNCTION `func_get_split_string`(
f_string varchar(1000),f_delimiter varchar(5),f_order int) RETURNS varchar(255) CHARSET utf8
BEGIN -- Get the separated number of given string.
declare result varchar(255) default '';
set result = reverse(substring_index(reverse(substring_index(f_string,f_delimiter,f_order)),f_delimiter,1));
return result;
END$$ DELIMITER ;测试的存储过程:
DELIMITER $$ CREATE PROCEDURE `sp_print_result`(
IN f_string varchar(1000),IN f_delimiter varchar(5)
)
BEGIN
-- Get the separated string.
declare cnt int default 0;
declare i int default 0;
set cnt = func_get_split_string_total(f_string,f_delimiter);
drop table if exists tmp_print;
create temporary table tmp_print (num int not null);
while i < cnt
do
set i = i + 1;
insert into tmp_print(num) values (func_get_split_string(f_string,f_delimiter,i));
end while;
select * from tmp_print;
END$$ DELIMITER ;2、来做一下测试
mysql> call sp_print_result('1,2,44,5,666,29232',',');
+-------+
| num |
+-------+
| 1 |
| 2 |
| 44 |
| 5 |
| 666 |
| 29232 |
+-------+
6 rows in set (0.01 sec)Query OK, 0 rows affected (0.01 sec)
windows 本地机器上成功测试
但是可信 linux 发现mysql 5.0.45 版本的等创建立函数不成功他不支持创建函数。 在不支持函数的mysql如何做这个字符处理??
SQL:
SELECT NEWDD,LOCATE(',',CC,2) AS DF1,
CAST(MID(CC,2,LOCATE(',',CC,2)-2) AS CHAR(100)) AS DF2
FROM (
SELECT A.*,B.ID AS ID1,MID(NEWDD,B.ID,LENGTH(NEWDD)) AS CC FROM (
SELECT CONCAT(',',favuser,',') AS NEWDD, id
FROM ttH2) A INNER JOIN LSB1 B ON B.ID<=LENGTH(A.NEWDD)
WHERE MID(MID(NEWDD,B.ID,LENGTH(NEWDD)),1,1)=',') A1
WHERE LENGTH(CC)>1
ORDER BY NEWDD;TTH2:
字段favuser,内容
10,12,13,15,52,3,4
CREATE PROCEDURE `MS_Data`.`P1` (
out a varchar(45),
)
BEGIN
declare b int default 0;
select COUNT(*) into b from t_users;
select b into a;
END可以用存储过程那把前面的 函数给替换掉的
BEGIN set total= 1+(length(f_string) - length(replace(f_string,f_delimiter,''))); END;CREATE PROCEDURE `func_get_split_string`( f_string varchar(1000),f_delimiter varchar(5),f_order int,out result varchar(255) )
BEGIN set result = reverse(substring_index(reverse(substring_index(f_string,f_delimiter,f_order)),f_delimiter,1)); END;CREATE PROCEDURE `sp_print_result`(IN f_string varchar(1000),IN f_delimiter varchar(5))
BEGIN declare cnt int default 0; declare i int default 0; declare total int;declare result varchar(255) default ''; call func_get_split_string_total(f_string,f_delimiter, total); drop table if exists tmp_print; create temporary table tmp_print (num int not null); while i < total do set i = i + 1; call func_get_split_string(f_string,f_delimiter,i,result); insert into tmp_print(num) values (result); end while; select * from tmp_print; END;
测试:
call sp_print_result('1,2,44,5,666,29232',',');