SQL Server - Split column data and retrieve last second value

I have a column name MasterCode in XYZ Table where data is stored in below form.

.105248.105250.104150.111004.

Now first of all I want to split the data into :

Then after to retrieve only last second value from the above.

So In the above given array, value returned should be 104150.

Solution

Use a split string function, but not the built in once since it will return only the values and you will lose the location data.

You can use Jeff Moden's DelimitedSplit8K that will return the item and the item index:

CREATE FUNCTION [dbo].[DelimitedSplit8K]
--===== Define I/O parameters
        (@pString VARCHAR(8000), @pDelimiter CHAR(1))
--WARNING!!! DO NOT USE MAX DATA-TYPES HERE!  IT WILL KILL PERFORMANCE!
RETURNS TABLE WITH SCHEMABINDING AS
 RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 1 up to 10,000...
     -- enough to cover VARCHAR(8000)
  WITH E1(N) AS (
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
                ),                          --10E+1 or 10 rows
       E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
       E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
 cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front
                     -- for both a performance gain and prevention of accidental "overruns"
                 SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
                ),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
                 SELECT 1 UNION ALL
                 SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter
                ),
cteLen(N1,L1) AS(--==== Return start and length (for use in substring)
                 SELECT s.N1,
                        ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)
                   FROM cteStart s
                )
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
 SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
        Item       = SUBSTRING(@pString, l.N1, l.L1)
   FROM cteLen l
;

Then you can use it to split the string and it will return a table like this:

DECLARE @string varchar(100) = '.105248.105250.104150.111004.';

SELECT *
FROM [dbo].[DelimitedSplit8K](@string, '.')

ItemNumber  Item
1   
2           105248
3           105250
4           104150
5           111004
6

You want only the parts where there actually is an item, so add a where clause, and you want the second from last so add row_number(), and you want the entire thing in a common table expression so that you can query it:

DECLARE @string varchar(100) = '.105248.105250.104150.111004.';

WITH CTE AS
(
    SELECT Item, ROW_NUMBER() OVER(ORDER BY ItemNumber DESC) As rn
    FROM [dbo].[DelimitedSplit8K](@string, '.')
    WHERE Item <> ''
)

And the query:

SELECT Item
FROM CTE
WHERE rn = 2

Result: 104150