Jeff Moden (10/16/2016)
Eirikur Eiriksson (10/15/2016)
Here is a more efficient alternative, beats the c..p out of the string splitting functionsVERY cool concept but, in this case, it appears to be coming up with an incorrect result set. Yes, I absolutely agree that it has successfully split out the two parts correctly but it's spitting them out on the same row. The requirement, if I'm reading the original post correctly, is that each of the parts should reside on a separate row.
Good catch Jeff, thanks. Here is a corrected version to fit the OP's requirements.
😎
USE TEEST;
GO
SET NOCOUNT ON;
;WITH SAMPLE_DATA AS
(
SELECT (SMPL_STR) FROM
(
VALUES('45 | 00055 | 9/30/2016 | Vodafone | Jack Morris | Newyork | Test Msg | TBL101 | PC | 1.00 | COMP101 | CS | 1.00.............. etc')
,('46 | xxx | not | enough | delims')
) AS X(SMPL_STR)
)
,DELIMITER_COUNT(DLMCNT,SMPL_STR) AS
(
SELECT
LEN(SD.SMPL_STR) - LEN(REPLACE(SD.SMPL_STR,CHAR(124),'')) AS DLMCNT
,SD.SMPL_STR AS SMPL_STR
FROM SAMPLE_DATA SD
)
,CHOPPED_STRINGS AS
(
SELECT
CASE
WHEN DC.DLMCNT > 6 THEN SUBSTRING(DC.SMPL_STR,1,CI7.CI - 1)
ELSE DC.SMPL_STR
END AS PART_ONE
,CASE
WHEN DC.DLMCNT > 6 THEN SUBSTRING(DC.SMPL_STR,CI7.CI + 1,LEN(DC.SMPL_STR))
ELSE NULL
END PART_TWO
FROM DELIMITER_COUNT DC
CROSS APPLY (SELECT CHARINDEX(CHAR(124),DC.SMPL_STR, 1) ) CI1(CI)
CROSS APPLY (SELECT CHARINDEX(CHAR(124),DC.SMPL_STR,CI1.CI + 1) ) CI2(CI)
CROSS APPLY (SELECT CHARINDEX(CHAR(124),DC.SMPL_STR,CI2.CI + 1) ) CI3(CI)
CROSS APPLY (SELECT CHARINDEX(CHAR(124),DC.SMPL_STR,CI3.CI + 1) ) CI4(CI)
CROSS APPLY (SELECT CHARINDEX(CHAR(124),DC.SMPL_STR,CI4.CI + 1) ) CI5(CI)
CROSS APPLY (SELECT CHARINDEX(CHAR(124),DC.SMPL_STR,CI5.CI + 1) ) CI6(CI)
CROSS APPLY (SELECT CHARINDEX(CHAR(124),DC.SMPL_STR,CI6.CI + 1) ) CI7(CI)
)
SELECT
STRING_PARTS.PART
FROM CHOPPED_STRINGS
CROSS APPLY
(
SELECT PART_ONE UNION ALL
SELECT PART_TWO
) STRING_PARTS(PART)
;
Output
PART
---------------------------------------------------------------------
45 | 00055 | 9/30/2016 | Vodafone | Jack Morris | Newyork | Test Msg
TBL101 | PC | 1.00 | COMP101 | CS | 1.00.............. etc
46 | xxx | not | enough | delims
NULL