SQL SERVER delimiter

Source: Internet
Author: User
Tags rtrim

1. Use the specified string to separate the elements. The number of elements after the split is returned.
Create function Get_StrLength
(
@ Str varchar (1024 ),
@ Split varchar (10)
)
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 method: select dbo. Get_StrLength ('7, 5, 6, 7, a, f, D ',',')

2. Split the string according to the specified symbol, and return the nth element of the specified index after the split, just like the Array

Create function Get_StrOfIndex
(
@ Str varchar (1024 ),
@ Split varchar (10 ),
@ Index int
)
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
-- Note: There are two situations: 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.

If @ location = 0
Select @ location = len (@ str) + 1
Return substring (@ str, @ start, @ location-@ start)
End
Call method: select dbo. Get_StrOfIndex ('8, 9, 3, 3, 4, 5', ',', 26)
3. combine the two functions above to return the split element.
Create function f_splitstr
(
@ SourceSql varchar (8000 ),
@ StrSeprate varchar (100)
)
Returns @ temp table (F1. varchar (100 ))
As
Begin
Declare @ ch as varchar (100)
Set @ SourceSql = @ SourceSql + @ StrSeprate
While (@ SourceSql <> '')
Begin
Set
@ Ch = left (@ SourceSql, Charindex (',', @ SourceSql, 1)-1)
Insert @ temp values (@ ch)
Set @ SourceSql = stuff (@ SourceSql, 1, charindex (',', @ SourceSql, 1 ),'')
End
Return
End
Call method: select * from f_splitstr ('1, 2, 3, 4, 5, 6 ',',')

Transferred from:

Http://zhiwenweb.cn/jszx/sjkjs/mssql/201110/29418.html

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.