Create a function of an SQL Array

Set ansi_nulls on
Set quoted_identifier on
Go

-- ===================================================== ======

-- Author: netcorner

-- Create Date: 3/10/2008

-- Description: Split string variant with type of ntext

-- ===================================================== ======

Create Function [DBO]. [split]

(

@ Text ntext,

@ Delimiter char (1)

)

Returns @ arraytable table (idx bigint, [value] nvarchar (200 ))

As

Begin

Declare @ splitlen int
Declare @ TMP varchar (1024)

Set @ splitlen = 4000

Declare @ idx int set @ idx = 0

-- Define the starting position of the substring

Declare @ textsplit bigint

Set @ textsplit = 1

While (@ textsplit <= datalength (@ text ))

Begin

-- Because many string processing functions cannot be used for ntext Data Types

-- Therefore, ntext Strings need to be processed cyclically in batches and retrieved in batches.

-- Characters are placed in nvarchar (4000) type variables.

Declare @ string nvarchar (4000)

Select @ string = substring (@ text, @ textsplit, @ splitlen)

-- Can retrieve all characters

If Len (@ string) = @ splitlen

Begin

-- Ensure that the extracted characters are a complete string combination separated by delimiters.

Declare @ lastcomma int

Select @ lastcomma = charindex (@ delimiter, reverse (@ string), 1)

-- The strings following the last separator are incomplete and should be discarded.

If @ lastcomma> 0

Begin

Select @ string = substring (@ string, 1, @ splitlen-@ lastcomma)

-- Set the starting position of the next character extraction from @ text

Select @ textsplit = @ textsplit + @ splitlen-@ lastcomma + 1

End

-- The string following the last separator is complete.

Else

Begin

Select @ textsplit = @ textsplit + @ splitlen + 1

End

End

-- Remove less than characters

Else

Begin

Select @ textsplit = @ textsplit + @ splitlen + 1

End

-- Parse @ string and retrieve the substring with the delimiter as the limit

Declare @ I1 int set @ I1 = 1

Declare @ I2 int set @ I2 = 1

While @ I1 <= Len (@ string)

Begin

Set @ I2 = charindex (@ delimiter, @ string, @ I1)
If @ I2 = 0
Set @ I2 = Len (@ string) + 1
Set @ TMP = substring (@ string, @ I1, @ I2-@ I1)
If (@ TMP = '')
Insert @ arraytable (idx) Select @ idx
Else
Begin
Insert @ arraytable (idx, value)
Select @ idx, @ TMP
End
Set @ I1 = @ I2 + 1

Set @ idx = @ idx + 1

End

End

Return

End

# Database Technology