Several commonly used SQL string splitting Functions

Source: Internet
Author: User
-- Split a string with the specified character and input a string to determine whether the passed string exists in the string array after the split
Create function SplitString (@ expression varchar (500), @ char varchar (10), @ str varchar (50 ))
Returns bit
As
Begin
-- If the input string is null, 0 is returned.
If (isnull (@ expression, '') = '')
Return 0
Declare @ beginIndex int, @ endIndex int -- truncates the start index and end index of a substring.
Declare @ Count int -- record the number of cycles
Set @ beginIndex = 1 -- Note: The index of a string in SQL starts from 1.
Set @ Count = 0
While (1 = 1)
Begin
Set @ Count = @ Count + 1
Set @ endIndex = charindex (@ char, @ expression, @ beginIndex)
-- If no separator is found, the entire string to be split is compared with the string to be judged.
If (@ endIndex = 0)
Begin
-- If the substring is intercepted for the first time
If (@ Count = 1)
Begin
If (@ expression = @ str)
Begin
Return 1
End
Begin
Return 0
End
End
Else -- if the substring is intercepted for the last time, the string following the last separator is obtained and compared with the string to be judged.
Begin
If (@ str = substring (@ expression, @ beginIndex, len (@ expression)-@ beginIndex + 1 ))
Begin
Return 1
End
Else
Begin
Return 0
End
End
End

-- If yes, 1 is returned.
If (@ str = substring (@ expression, @ beginIndex, @ endIndex-@ beginIndex ))
Begin
Return 1
End
Else -- if not matched, continue searching
Begin
Set @ beginIndex = @ endIndex + 1
End
End
Return 0
End

-- Split a string with the specified character and input an index. The string of the specified index after the split is returned (the index starts from 1)
Create function IndexOfArray (@ expression varchar (500), @ char varchar (10), @ Index int)
Returns varchar (200)
As
Begin
Declare @ beginIndex int, @ endIndex int -- truncates the start index and end index of a substring.
Declare @ Count int -- record the number of cycles
Set @ beginIndex = 1 -- Note: The index of a string in SQL starts from 1.
Set @ Count = 0
While (1 = 1)
Begin
Set @ Count = @ Count + 1
Set @ endIndex = charindex (@ char, @ expression, @ beginIndex)

-- If no separator is found, the substring from the last separator to the end of the string is truncated.
If (@ endIndex = 0)
Begin
Return substring (@ expression, @ beginIndex, len (@ expression)-@ beginIndex + 1)
End

-- If the index of the string intercepted this time is the specified index, the truncated string will be returned.
If (@ Count = @ Index)
Begin
Return substring (@ expression, @ beginIndex, @ endIndex-@ beginIndex)
End
Else -- otherwise continue
Begin
Set @ beginIndex = @ endIndex + 1
Continue
End
End
Return''
End
Go

-- Split a string with the specified character and input a string to determine the input string
-- Whether it is equal to the string of the specified index in the string array after the split
Create function SplitEquare (@ expression varchar (200), @ char varchar (10), @ Index int, @ str varchar (100 ))
Returns bit
As
Begin
Declare @ SplitStr varchar (100)
Set @ SplitStr = dbo. IndexOfArray (@ expression, @ char, @ Index)
If (@ SplitStr = @ str)
Begin
Return 1
End
Else
Begin
Return 0
End
Return 0
End
Go

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.