iVend Database Database : fnSplitValues User Defined Function

Creation Date

6/28/2017 11:56 AM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@String

In

VarWChar

1073741823

@TABLE_RETURN_VALUE

Return Value

Result table returned by table valued function

Empty

0

Database Object

Object Type

Description

Dep Level

rpt_LayAway

Stored Procedure

1

RPT_StoreAnalysis

Stored Procedure

1

rptSalesData

User Defined Function

1

rptSalesTransactionData

User Defined Function

1

rptSalesTransactionDataWithSalesPerson

User Defined Function

1

CREATE FUNCTION [dbo].[fnSplitValues]

(

@String nvarchar(max)

)

RETURNS

@SplitTable TABLE

(

DataString Nvarchar(Max)

)

/*

CREATED : Jyotsna Tyagi

CREATED DATED : 28th Feb, 2017

MODIFED : Jyotsna Tyagi

MODIFED DATED : 11th Apr, 2018

DECSRIPTION : To Split multiple filter parameter and avoid Like operator.

*/

AS

BEGIN

Declare @DataValue NVARCHAr(max), @Amp Nvarchar(Max)

SET @Amp = CONVERT(nvarchar(max), (SELECT @String FOR XML PATH('')), 1)

Set @DataValue=CASE WHEN LEN(@Amp)=0 THEN '' ELSE Replace(Left(Right(COALESCE(LTRIM(RTRIM(@Amp)),''), Len(COALESCE(LTRIM(RTRIM(@Amp)),''))-1), Len(Right(COALESCE(LTRIM(RTRIM(@Amp)),''), Len(COALESCE(LTRIM(RTRIM(@Amp)),''))-1))-1),N'☺',',')END

DECLARE @xml xml

SET @xml = '<r>' + replace(COALESCE(@DataValue,''),',','</r><r>') + '</r>'

INSERT INTO @SplitTable(DataString)

SELECT r.value('.','Nvarchar(max)')

FROM @xml.nodes('/r') as records(r)

RETURN

END