A function in Mysql to implement a split string similar to split _mysql

Source: Internet
Author: User
Tags rtrim
The function below implements the processing of strings like an array.

one, using a temporary table as an array

Copy Code code as follows:

Create function F_split (@c varchar), @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,1,charindex (@split, @c), "")
End
Insert @t (COL) VALUES (@c)
Return
End
Go
SELECT * from Dbo.f_split (' dfkd,dfdkdf,dfdkf,dffjk ', ', ')
Drop function F_split
Ao.
--------------------
Dfkd
Dfdkdf
Dfdkf
Dffjk
(The number of rows affected is 4 rows)


second, according to the specified symbol segmentation string, return the number of elements after the split, the method is very simple, is to see how many delimiters in the string, and then add one, is the result of the request.

Copy Code code as follows:

CREATE function Get_strarraylength
(
@str varchar (1024),--string to be split
@split varchar (10)--Separator symbol
)
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

Invocation Example: SELECT dbo. Get_strarraylength (' 78,1,2,3 ', ', ', ')
return value: 4

dividing a string by a specified symbol, returning the first element of the specified index after the partition, as convenient as an array

Copy Code code as follows:

CREATE function Get_strarraystrofindex
(
@str varchar (1024),--string to be split
@split varchar (10),--Separator symbol
@index INT--Take the first few elements
)
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, @str, @start)
Set @next = @next +1
End
If @location =0 select @location =len (@str) +1
There are two conditions: 1, the string does not exist separator symbol 2, there is a delimiter in the string, jumping out of the while loop, the @location is 0, the default is a string with a delimiter behind.
return substring (@str, @start, @location-@start)
End

Invocation Example: SELECT dbo. Get_strarraystrofindex (' 8,9,4 ', ', ', ', 2)
Return Value: 9

four, combining the top two functions, like arrays to traverse the elements in the string

Copy Code code as follows:

DECLARE @str varchar (50)
Set @str = ' 1,2,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
Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.