Wednesday, August 3, 2011

MSSQL - SPLIT String and get occurence position

CREATE FUNCTION [dbo].[SPLIT_GET]

(

@inputString NVARCHAR(max),

@delimiter CHAR,

@findposition INT

)

RETURNS NVARCHAR(MAX)

AS

BEGIN



DECLARE @startposition INT

DECLARE @endposition INT

DECLARE @inputLength INT

DECLARE @lengthToParse INT

DECLARE @currentposition INT

DECLARE @resultString NVARCHAR(MAX)



SET @currentposition = 0

SET @startposition = 1

SET @endposition = 1

SET @inputLength = LEN(@inputString)

SET @resultString = null



WHILE @startposition <= @inputLength 

BEGIN

SET @currentposition += 1

SELECT @endposition = charindex(@delimiter,@inputString,@startposition)

IF @endposition <> 0

BEGIN

SET @endposition = @endposition - 1

END

ELSE

BEGIN

SET @endposition = @inputLength

END



SET @lengthToParse = @endposition - @startposition + 1

IF @currentposition = @findposition

BEGIN

SET @resultString = substring(@inputString,@startposition,@lengthToParse)

BREAK

END



SET @startposition = @endposition + 2



END

RETURN @resultString



END
Sample usage: SELECT dbo.SPLIT_GET('mark.libres','.','2')
Will return "libres"

No comments:

Post a Comment