MySQL string splitting operation (string interception containing separators)

String extraction without delimiters

Question Requirements

Field value in the database:

Implementation effect: Need to turn one row of data into multiple rows

Implemented SQL

SELECT LEFT(SUBSTRING('P1111',help_topic_id+1),1) AS num FROM mysql.help_topic WHERE help_topic_id < LENGTH('P1111');

Knowledge points involved

1. String interception: SUBSTRING(str,pos)

1. Parameter Description

2. Examples

(1) Get all the characters before the second comma "," as the separator.

SUBSTRING_INDEX('7654,7698,7782,7788',',',2)

(2) Get all characters after the second to last comma separator ","

SUBSTRING_INDEX('7654,7698,7782,7788',',',-2)

2. Replacement function: replace(str, from_str, to_str)

1. Parameter explanation

2. Examples

(1) Replace the separator "," comma with "" space.

REPLACE('7654,7698,7782,7788',',','')

3. Get the string length: LENGTH( str )

1. Parameter explanation

2. Examples

(1) Get the length of the string '7654,7698,7782,7788'

LENGTH('7654,7698,7782,7788')

Implemented SQL parsing

SELECT 
 SUBSTRING_INDEX(SUBSTRING_INDEX('7654,7698,7782,7788',',',help_topic_id+1),',',-1) AS num 
FROM 
 mysql.help_topic 
WHERE 
 help_topic_id < LENGTH('7654,7698,7782,7788')-LENGTH(REPLACE('7654,7698,7782,7788',',',''))+1

Here, help_topic_id of the help_topic table of the MySQL library is used as a variable because help_topic_id is auto-incrementing. Of course, auto-incrementing fields of other tables can also be used as an aid.

help_topic table:

Implementation steps:

Step 1: First, get the number of strings that need to be split in the end, and use help_topic_id to simulate traversing the nth string.

The code snippet involved:

help_topic_id < LENGTH('7654,7698,7782,7788')-LENGTH(REPLACE('7654,7698,7782,7788',',',''))+1

Step 2: Split the string based on the comma "," using the SUBSTRING_INDEX (str, delim, count) function, and assign the result to the num field.

The code snippet involved:

SUBSTRING_INDEX(SUBSTRING_INDEX('7654,7698,7782,7788',',',help_topic_id+1),',',-1) AS num

first step:

Use the comma "," as the delimiter and intercept all the strings before the n+1th delimiter according to the value of help_topic_id. (Here n+1 is because help_topic_id starts from 0, and here we need to get it from the first separator.)

SUBSTRING_INDEX('7654,7698,7782,7788',',',help_topic_id+1)

For example:

When help_topic_id = 0, the obtained string = 7654

When help_topic_id = 1, the obtained string = 7654,7698

…(and so on)

Step 2:

Use the comma "," as the delimiter and extract all the strings after the last delimiter.

SUBSTRING_INDEX(SUBSTRING_INDEX('7654,7698,7782,7788',',',help_topic_id+1),',',-1)

For example:

According to the first step, when help_topic_id = 0, the obtained string = 7654, at this time the string intercepted in the second step = 7654

According to the first step, when help_topic_id = 1, the obtained string = 7654,7698, at this time the string intercepted in the second step = 7698

…(and so on)

Finally, the following effects were successfully achieved~

Note: For string splitting without delimiters, please refer to MySQL - String Splitting (String Interception without Delimiters)

Supplement: MySQL field delimiter split_A function similar to SPLIT in MySQL to split strings

The function below implements processing strings like arrays.

1. Using a temporary table as an array

create function f_split(@c varchar(2000),@split varchar(2))
returns @t table(col varchar(20))
as
begin
while(charindex(@split,@c)<>0)
begin
insert @t(col) values ​​(substring(@c,1,charindex(@split,@c)-1))
set @c = stuff(@c,@c),'')
end
insert @t(col) values ​​(@c)
return
end
go
select * from dbo.f_split('dfkd,dfdkdf,dfdkf,dffjk',',')
drop function f_split
col
--------------------
dfkd
dfdkdf
dfdkf
dfjk

(The number of rows affected is 4)

2. Split the string by the specified symbol

Returns the number of elements after segmentation. The method is very simple. Just look at how many separators there are in the string, and then add one, which is the required result.

CREATE function Get_StrArrayLength
(
@str varchar(1024),--the string to be split @split varchar(10) --delimiter)
returns int
as
begin
declare @location int
declare @start int
declare @length int
set @str=ltrim(rtrim(@str))
set @location=charindex(@split,@str)
set @length=1
while @location<>0
begin
set @start=@location+1
set @location=charindex(@split,@str,@start)
set @length=@length+1
end
return @length
end

Calling example:

select dbo.Get_StrArrayLength('78,2,3',')

Return value: 4

3. Split the string by the specified symbol

Returns the element at the specified index after segmentation, as convenient as an array

CREATE function Get_StrArrayStrOfIndex
(
@str varchar(1024),--the string to be split @split varchar(10),--separator @index int --the first element to be taken)
returns varchar(1024)
as
begin
declare @location int
declare @start int
declare @next int
declare @seed int
set @str=ltrim(rtrim(@str))
set @start=1
set @next=1
set @seed=len(@split)
set @location=charindex(@split,@str)
while @location<>0 and @index>@next
begin
set @start=@location+@seed
set @location=charindex(@split,@start)
set @next=@next+1
end
if @location =0 select @location =len(@str)+1
--There are two situations here: 1. There is no separator in the string 2. There is a separator in the string. After jumping out of the while loop, @location is 0, which means there is a separator after the string by default.
return substring(@str,@start,@location-@start)
end

Calling example:

select dbo.Get_StrArrayStrOfIndex('8,9,4',2)

Return value: 9

4. Combining the above two functions, traverse the elements in the string like an array

declare @str varchar(50)
set @str='1,3,4,5'
declare @next int
set @next=1
while @next<=dbo.Get_StrArrayLength(@str,')
begin
print dbo.Get_StrArrayStrOfIndex(@str,@next)
set @next=@next+1
end

Call result:

1

2

3

4

5

The above is my personal experience. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM. If there are any mistakes or incomplete considerations, please feel free to correct me.