This is a function written before, because writing SQL programs have encountered the case where you need to split strings. Provides such a function in VB, or String objects in other language class libraries are also available to Split functions in a string to an array. Perhaps because SQL has no array type, there is no such string handler. I wrote a custom function to implement similar functions.
SQL has no array type, but can be similar to the Table type value. It can be designed, the function input value is a parameter of a character type (VARCHAR), returns a table of a Table type.
Defined as follows:
1, parameter definition and description
Output parameters: @splitstring varchar (8000) - Split string @separate varchar (10) --- Delivery
Returns: @returntable table (20)) - A single character length can be modified according to you
2, function body
Create Function Splitstr (@splitstring varchar (8000), @separate varchar (10)) Returns @returntable table (col_value varchar (20)) AS
Begin
Declare @thissplitstr varchar (20) Declare @thissepindex INTDECLARE @LastSepindex Int
Set @lastsepindex = 0
IF right (@SPLITSTRING, LEN (@separate)) <> @separate set @Splitstring = @Splitstring @separateSet @thissepindex = charindex (@ separate, @ splitstring, @ lastsepindex)
while @lastSepIndex <= @thisSepIndexbegin set @thisSplitStr = SubString (@splitString, @ lastSepIndex, @ thisSepIndex- @ lastSepIndex) set @lastSepIndex = @thisSepIndex 1 set @thisSepIndex = CharIndex (@ separate, @ splitString, @ lastSepIndex) insert into @Returntable VALUES (@thissplitstr) endreturn
End
The following example:
Use example: Select * from splitstr ('123, 456, 789,', ')
result:
Col_Value ------------------------------------------------- ---------------------------- 123456789
