Somashekhar Kendule Somashekhar Kendule - 1 month ago 11
SQL Question

Split sql string value based on 7th delimiter

Present String value

String

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

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
Comments