Want to split the string while pulling data from Source using T-SQL. Don't want to use Function as not allowed to create function in Source System.

Question

Hi All,
Is there any easy way to Split the below string and make it separate column for each value..?

I have SQL Server 2016 and Source is SQL Server 2014. It is not allowed to create Function in source System. I want to split string while pulling data and not to stage it, split and then give to end user.

This is how it is coming from Source:
EmpId-FirstName-LastName-Dpt-Designation-DOB-Skill
222-Shiv-Kumar-IT-DWH-March-BI
333-Ram--IT--June-

I want output like:

https://www.mssqltips.com/sqlservertip/4884/sql-server-2016-stringsplit-function/
I have this function but want to avoid using it:

SELECT DISPLAYVALUE,
[dbo].Wordparser [EmpId],
[dbo].Wordparser [FirstName],
[dbo].Wordparser [LastName],
[dbo].Wordparser [Dpt],
[dbo].Wordparser [Designation],
[dbo].Wordparser [DOB],
[dbo].Wordparser [Skill]

ALTER FUNCTION [dbo].[Wordparser]

(
@multiwordstring VARCHAR(255),
@wordnumber NUMERIC
)
returns VARCHAR(255)
AS
BEGIN
DECLARE @remainingstring VARCHAR(255)
SET @remainingstring=@multiwordstring

  DECLARE @numberofwords NUMERIC  
  SET @numberofwords=(LEN(@remainingstring) - LEN(REPLACE(@remainingstring, '-', '')) + 1)  
 
  DECLARE @word VARCHAR(50)  
  DECLARE @parsedwords TABLE  
  (  
     line NUMERIC IDENTITY(1, 1),  
     word VARCHAR(255)  
  )  
 
  WHILE @numberofwords > 1  
    BEGIN  
        SET @word=LEFT(@remainingstring, CHARINDEX('-', @remainingstring) - 1)  
 
        INSERT INTO @parsedwords(word)  
        SELECT @word  

IF
@omarcanchanya = ''
SET @remainingstring= RIGHT(@remainingstring,LEN(@remainingstring)-1)

ELSE
SET @remainingstring= REPLACE(@remainingstring, Concat(@omarcanchanya , '-'), '')

        SET @numberofwords=(LEN(@remainingstring) - LEN(REPLACE(@remainingstring, '-', '')) + 1)  
 
        IF @numberofwords = 1  
          BREAK  
 
        ELSE  
          CONTINUE  
    END  
 
  IF @numberofwords = 1  
    SELECT @word = @remainingstring  
  INSERT INTO @parsedwords(word)  
  SELECT @word  
 
  RETURN  
    (SELECT word  
     FROM   @parsedwords  
     WHERE  line = @wordnumber)  

END

Answer 1

 create table test (col varchar(255))
insert into test values ('222-Shiv-Kumar-IT-DWH-March-BI')
,('333-Ram--IT--June-')


;WITH cte
AS (
SELECT CAST(N'<H><r>' +  Replace(col,'-', '</r><r>') + '</r></H>' as XML) AS vals 
from test
)

SELECT DISTINCT S.a.value('(/H/r)[1]', 'NVARCHAR(50)') AS [EmpId],
S.a.value('(/H/r)[2]', 'NVARCHAR(50)') AS [FirstName],
S.a.value('(/H/r)[3]', 'NVARCHAR(50)') AS [LastName],
S.a.value('(/H/r)[4]', 'NVARCHAR(50)') AS [Dpt],
S.a.value('(/H/r)[5]', 'NVARCHAR(50)') AS [Designation],
S.a.value('(/H/r)[6]', 'NVARCHAR(50)') AS [DOB],
S.a.value('(/H/r)[7]', 'NVARCHAR(50)') AS [Skill]



FROM cte CROSS APPLY vals.nodes('/H/r') S(a);


 drop table test

Answer 2

Hi @Shivendoo Kumar ，

Both built-in functions(string_split) and custom functions are good choices. I tried not to use functions, but the results were not very good：

    create table test (col varchar(255))  
    insert into test values('EmpId-FirstName-LastName-Dpt-Designation-DOB-Skill'),  
    ('222-Shiv-Kumar-IT-DWH-March-BI'),('333-Ram--IT--June-')  
  
 ;with cte1 as (  
 select  c.value AS col ,c.[key] as rn  
 from test   
 cross apply openjson(('["'+ REPLACE(col, '-', '","') + '"]')) c  
 )  
  
select case when rn=0 then col end col,  
case when rn=1 then col end,  
case when rn=2 then col end,  
case when rn=3 then col end,  
case when rn=4 then col end,  
case when rn=5 then col end,  
case when rn=6 then col end from cte1   
  
drop table test   

Best Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Also check an example that uses string functions:

declare @mytable as table ( displayvalue varchar(1000) )

insert into @mytable values
( '222-Shiv-Kumar-IT-DWH-March-BI' ),
( '333-Ram--IT--June-' )


;
with Q as 
(
    select displayvalue + '-------' as s
    from @mytable
)
select 
    SUBSTRING(s, 1, t1.i - 1 - 1)       as EmpId,
    SUBSTRING(s, t1.i, t2.i - t1.i - 1) as FirstName,
    SUBSTRING(s, t2.i, t3.i - t2.i - 1) as LastName,
    SUBSTRING(s, t3.i, t4.i - t3.i - 1) as Dpt,
    SUBSTRING(s, t4.i, t5.i - t4.i - 1) as Designation,
    SUBSTRING(s, t5.i, t6.i - t5.i - 1) as DOB,
    SUBSTRING(s, t6.i, t7.i - t6.i - 1) as Skill
from Q
cross apply (values (CHARINDEX('-', s, 1)    + 1)) t1(i)
cross apply (values (CHARINDEX('-', s, t1.i) + 1)) t2(i)
cross apply (values (CHARINDEX('-', s, t2.i) + 1)) t3(i)
cross apply (values (CHARINDEX('-', s, t3.i) + 1)) t4(i)
cross apply (values (CHARINDEX('-', s, t4.i) + 1)) t5(i)
cross apply (values (CHARINDEX('-', s, t5.i) + 1)) t6(i)
cross apply (values (CHARINDEX('-', s, t6.i) + 1)) t7(i)