iVend Database Database : SecondLastItem User Defined Function

Creation Date

6/21/2016 6:14 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@Input

In

VarWChar

1073741823

@Character

In

VarChar

1

@RETURN_VALUE

Return Value

VarWChar

1073741823

Create FUNCTION [dbo].[SecondLastItem]

(

  @Input NVARCHAR(MAX),

    @Character CHAR(1)

)

RETURNS NVARCHAR(Max)

AS

BEGIN

DECLARE @text VARCHAR(MAX)

DECLARE @start_index INT

DECLARE @end_index INT

SET @text = REVERSE(@Input)

SELECT @start_index = CHARINDEX('.', @text, 1)

IF @start_index <=0 RETURN NULL

SELECT @end_index = CHARINDEX('.', @text, CHARINDEX('.', @text, 1) + 1)

IF @end_index <= 0

RETURN REVERSE(SUBSTRING(@text, @start_index + 1, Len(@text)-@start_index))

RETURN REVERSE(SUBSTRING(@text, @start_index + 1, @end_index - @start_index - 1))

END