StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Looks like SQL Server code. If so, Here's a UDF to convert a delimited list into a table:</p> <pre><code>Create Function [dbo].[ParseTextString] (@S Text, @delim VarChar(5)) Returns @tOut Table (ValNum Integer Identity Primary Key, sVal VarChar(8000)) As Begin Declare @dLLen TinyInt -- Length of delimiter Declare @sWin VarChar(8000) -- Will Contain Window into text string Declare @wLen Integer -- Length of Window Declare @wLast TinyInt -- Boolean to indicate processing Last Window Declare @wPos Integer -- Start Position of Window within Text String Declare @sVal VarChar(8000) -- String Data to insert into output Table Declare @BtchSiz Integer -- Maximum Size of Window Set @BtchSiz = 7900 -- (Reset to smaller values to test routine) Declare @dPos Integer -- Position within Window of next Delimiter Declare @Strt Integer -- Start Position of each data value within Window -- ------------------------------------------------------------------------- -- Default delimiter is pipe char ----- If @delim is Null Set @delim = '|' If DataLength(@S) = 0 Or Substring(@S, 1, @BtchSiz) = @delim Return -- --------------------------- Select @dLLen = Len(@delim), @Strt = 1, @wPos = 1, @sWin = Substring(@S, 1, @BtchSiz) Select @wLen = Len(@sWin), @wLast = Case When Len(@sWin) = @BtchSiz Then 0 Else 1 End, @dPos = CharIndex(@delim, @sWin, @Strt) -- ---------------------------- While @Strt <= @wLen Begin If @dPos = 0 -- No More delimiters in window Begin If @wLast = 1 Set @dPos = @wLen + 1 Begin Set @wPos = @wPos + @Strt - 1 Set @sWin = Substring(@S, @wPos, @BtchSiz) -- ---------------------------------------- Select @wLen = Len(@sWin), @Strt = 1, @wLast = Case When Len(@sWin) = @BtchSiz Then 0 Else 1 End, @dPos = CharIndex(@delim, @sWin, 1) If @dPos = 0 Set @dPos = @wLen + 1 End End -- ------------------------------- Set @sVal = LTrim(Substring(@sWin, @Strt, @dPos - @Strt)) Insert @tOut (sVal) Values (@sVal) -- ------------------------------- -- Move @Strt to char after last delimiter Set @Strt = @dPos + @dLLen Set @dPos = CharIndex(@delim, @sWin, @Strt) End Return End </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload