TSQL: user defined function split

CREATE FUNCTION [dbo].[udfSplit](@text nvarchar(max), @delimiter char(1) = ‘ ‘)

RETURNS @Strings TABLE ( position int IDENTITY PRIMARY KEY, value nvarchar(max) )

AS

BEGIN

DECLARE @index int

SET @index = -1

WHILE (LEN(@text) > 0)

BEGIN SET @index = CHARINDEX(@delimiter , @text)

IF (@index = 0) AND (LEN(@text) > 0)

BEGIN

INSERT INTO @Strings VALUES (@text) BREAK

END

IF (@index > 1)

BEGIN

INSERT INTO @Strings VALUES (LEFT(@text, @index – 1))

SET @text = RIGHT(@text, (LEN(@text) – @index))

END

ELSE

SET @text = RIGHT(@text, (LEN(@text) – @index))

END

RETURN

END

parameters:

@text: text to split

@delimiter: character

returns:

table with columns position und value

SELECT * FROM [OLReweAbf].[dbo].[udfSplit] (‘test’,’t’)

position value
1 es

example:

Update LBFulfillmentKNORueckmeldungAuftrag

SET PaketGew = (select sum(cast([value] as bigint)) from [dbo].[udfSplit](PaketGew,’:’))

WHERE VersandartID LIKE ‘LKW%’ AND PaketGew LIKE ‘%:%’ AND TrackingNr is NULL

Advertisements

2 responses to “TSQL: user defined function split

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s