Home > Database > Mysql Tutorial > How to store mysql string fields according to comma interception

How to store mysql string fields according to comma interception

一个新手
Release: 2017-09-08 10:38:01
Original
1728 people have browsed it

I have been looking for it for a long time. I want to use mysql to split the string into multiple data using ','. I must use a stored procedure to achieve this function. Let's implement this function here, for example,


Convert to


Exclude the N inside, and then extract the number:

#查看已存在的存储过程:SHOW PROCEDURE STATUS
Copy after login
# 函数:func_split_TotalLength 
DELIMITER $$ 
DROP function IF EXISTS `func_split_TotalLength` $$ 
CREATE FUNCTION `func_split_TotalLength` 
(f_string varchar(1000),f_delimiter varchar(5)) RETURNS int(11) 
BEGIN
Copy after login
# 计算传入字符串的总length 
return 1+(length(f_string) - length(replace(f_string,f_delimiter,''))); 
END $$ 
DELIMITER;
Copy after login
# 函数:func_split 
DELIMITER $$ 
DROP function IF EXISTS `func_split` $$ 
CREATE FUNCTION `func_split` 
(f_string varchar(1000),f_delimiter varchar(5),f_order int) RETURNS varchar(255) CHARSET utf8 
BEGIN
Copy after login
 # 拆分传入的字符串,返回拆分后的新字符串 
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;
Copy after login
# 存储过程:splitString 
DELIMITER $$ 
DROP PROCEDURE IF EXISTS `splitString` $$ 
CREATE PROCEDURE `splitString` 
(IN f_string varchar(1000),IN f_delimiter varchar(10)) 
BEGIN
Copy after login
# 拆分结果 declare cnt int default 0; declare i int default 0; set cnt = func_split_TotalLength(f_string,f_delimiter); DROP TABLE IF EXISTS `tmp_split`; create temporary table `tmp_split` (`status` varchar(128) not null) DEFAULT CHARSET=utf8; while i < cnt do 
    set i = i + 1;
Copy after login
#插入除了&#39;N&#39;之外的所有数据
IF func_split(f_string,f_delimiter,i) != &#39;N&#39; THEN
insert into tmp_split(`status`) values (func_split(f_string,f_delimiter,i)); END IF; 
end while; 
END$$ 
DELIMITER;
Copy after login
call splitString( (SELECT overdue_record_2year FROM Loan l WHERE l.papers_id = 231203199305030219 ORDER BY papers_id DESC LIMIT 1) ,","); 
select * from tmp_split;
Copy after login

The above is the detailed content of How to store mysql string fields according to comma interception. For more information, please follow other related articles on the PHP Chinese website!

Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template