MySQL-MySQL tutorial

Source: Internet
Author: User
In MySQL, the bitsCN.com function, similar to the SPLIT string, is implemented to process strings like arrays.

I. use 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, 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
Col
--------------------
Dfkd
Dfdkdf
Dfdkf
Dffjk
(The number of affected rows is 4)


2. split the string by the specified symbol and return the number of elements after the split. the method is very simple, that is, to check the number of separators in the string, and then add one, which is the required result.


CREATE function Get_StrArrayLength
(
@ Str varchar (1024), -- string to be split
@ Split varchar (10) -- delimiter number
)
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

Call example: select dbo. Get_StrArrayLength ('78, 1, 2, 3 ',',')
Return value: 4

3. split the string by the specified symbol. return the nth element of the specified index after the split, which is as convenient as an array.


CREATE function Get_StrArrayStrOfIndex
(
@ Str varchar (1024), -- string to be split
@ Split varchar (10), -- separator
@ Index int -- obtains the nth element.
)
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 cases: 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, by default, there is a separator behind the string.
Return substring (@ str, @ start, @ location-@ start)
End

Call example: select dbo. Get_StrArrayStrOfIndex ('8, 9, 4', ',', 2)
Return value: 9

4. combine the above two functions to traverse the elements in the string like an array


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
5bitsCN.com

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.