Split sql string value based on 7th delimiter

Question

Present column value

(Below is column value from temp table, value here is dynamically changing)

45 | 00055 | 9/30/2016 | Vodafone | Randy Singh | Newyork | Test Msg | TBL101 | PC | 1.00 | COMP101 | CS | 1.00.............. etc

Need to divide based on 7th PIPE i.e after Test Msg

Output should be

String1

45 | 00055 | 9/30/2016 | Vodafone | Randy Singh | Newyork | Test Msg

and (as a second string)

String 2

TBL101 | PC | 1.00 | COMP101 | CS | 1.00......... etc

Function

CREATE FUNCTION dbo.SUBSTRING_INDEX
(
   @str NVARCHAR(4000),
   @delim NVARCHAR(1),
   @count INT
)
RETURNS NVARCHAR(4000)
WITH SCHEMABINDING
BEGIN
   DECLARE @XmlSourceString XML;
   SET @XmlSourceString = (SELECT N'<root><row>' + REPLACE( (SELECT @str AS '*' FOR XML PATH('')) , @delim, N'</row><row>' ) + N'</row></root>');
RETURN STUFF
(
    ((
        SELECT  @delim + x.XmlCol.value(N'(text())[1]', N'NVARCHAR(4000)') AS '*'
        FROM    @XmlSourceString.nodes(N'(root/row)[position() <= sql:variable("@count")]') x(XmlCol)
        FOR XML PATH(N''), TYPE
    ).value(N'.', N'NVARCHAR(4000)')), 
    1, 1, N''
);
END

GO

DECLARE @EmpId NVARCHAR(1000)
select @EmpId = temp from OMSOrderTemp


SELECT dbo.SUBSTRING_INDEX(@EmpId, N'|', 7) AS Result;e

Here in Result only string1 is showing and only first row.

Answer 1

Spend time for you and happy that come with solution , I have modify the your function with own logic you can try this, This is table value function i.e this function will return Table

CREATE FUNCTION dbo.SUBSTRING_INDEX
(
  @str NVARCHAR(4000),
  @delim NVARCHAR(1),
  @count INT
)RETURNS @rtnTable TABLE 
(
   FirstString  NVARCHAR(2000),
   SecondString NVARCHAR(2000)
)
AS
BEGIN
    DECLARE @cnt INT=1;
    DECLARE @subStringPoint INT = 0
    WHILE @cnt <=@count
    BEGIN 
            SET @subStringPoint=CHARINDEX(@delim,@str,@subStringPoint)+1
            SET @cnt=@cnt+1
    END

    INSERT INTO @rtnTable
    SELECT SUBSTRING(@str,0,@subStringPoint-1) ,SUBSTRING(@str,@subStringPoint+1,LEN(@str)) 
    RETURN
END 

To call this function

DECLARE @s varchar(MAX)='45 | 00055 | 9/30/2016 | Vodafone | Randy Singh | Newyork | Test Msg | TBL101 | PC | 1.00 | COMP101 | CS | 1.00'
SELECT * FROM dbo.SUBSTRING_INDEX (@s,'|',7)

This will gives two column output

45 | 00055 | 9/30/2016 | Vodafone | Randy Singh | Newyork | Test Msg    TBL101 | PC | 1.00 | COMP101 | CS | 1.00

Answer 2

Finally got a solution almost identical to @JaydipJ. I thought to implement in a different way but the following should do using While loop:

DECLARE @str VARCHAR(1000), 
        @str1 VARCHAR(1000), 
        @str2 VARCHAR(1000), 
        @pos INT, 
        @counter INT 

SET @str = '45 | 00055 | 9/30/2016 | Vodafone | Randy Singh | Newyork | Test Msg | TBL101 | PC | 1.00 | COMP101 | CS | 1.00.............. etc' 

SET @counter = 0 
SET @pos = 0 

WHILE @counter < 7 
BEGIN   
SET @pos = CHARINDEX('|', @str, @pos + 1) ---- Gets the position of delimiter '|'
SET @counter = @counter + 1 ---- Increments the counter on the given counter value
END 

SET @str1 = SUBSTRING(@str, 1, @pos) ---- Splits the string on the 7th position of delimiter '|'
SET @str2 = SUBSTRING(@str, @pos + 1, LEN(@str) - @pos) ---- Splits the rest of the string

Print 'str1='+ @str1 
Print 'str2='+ @str2 

SELECT @str1 AS String1, @str2 AS String2 

The While loop is used to iterate through the string and getting the Delimiter position, it splits the string.