locked
How to separate a column with a hyphen value into two parts? Please help! RRS feed

  • Question

  • Hi,
      I have a Employee table and there is a column called EmpID which has the values like 100AS-12U, 189722,123-yure12, qswer-23 . that means, some of the values have two parts with a - in between. The place of the - may vary from value to value. My requirement is, I have to create two computed columns from this. First column is before the - and the second column is after the -. if it does not have any - in the value (Eg:189722), it should go to the first column. Here are the DDL, with values and also the output required. Could you please help me to get the requirement done? thanks in advance!

    ---Drop table #Temp_Emp
    ---truncate table #Temp_Emp

    Create table #Temp_Emp (EmpID varchar(30))
    go

    insert into #Temp_Emp  values ('100AS-12U')
    insert into #Temp_Emp  values ('189722')
    insert into #Temp_Emp  values ('123-yure12')
    insert into #Temp_Emp  values ('qswer-23')

    --select * from #Temp_Emp 

    ---Requirement

    Select '100AS' as COl1,'12U' as COl2 union
    Select '189722' as COl1,'' as COl2 union
    Select '123' as COl1,'yure12' as COl2 union
    Select 'qswer' as COl1,'23' as COl2

    Tuesday, August 12, 2014 1:56 PM

Answers

  • Can you try the below code?

    Create table #Temp_Emp (EmpID varchar(30))
    go
    
    insert into #Temp_Emp  values ('100AS-12U')
    insert into #Temp_Emp  values ('189722')
    insert into #Temp_Emp  values ('123-yure12')
    insert into #Temp_Emp  values ('qswer-23')
    
    select PARSENAME(replace(empid,'-','.'),2) col1,PARSENAME(replace(empid,'-','.'),1)col2 from #Temp_Emp
    --Prashanth

    • Marked as answer by Manesar Tuesday, August 12, 2014 8:10 PM
    • Unmarked as answer by Manesar Tuesday, August 12, 2014 8:10 PM
    • Marked as answer by Manesar Tuesday, August 12, 2014 8:10 PM
    Tuesday, August 12, 2014 2:31 PM
  • Create table #Temp_Emp (EmpID varchar(30))
    go
    
    insert into #Temp_Emp  values ('100AS-12U')
    insert into #Temp_Emp  values ('189722')
    insert into #Temp_Emp  values ('123-yure12')
    insert into #Temp_Emp  values ('qswer-23')
    
    --select * from #Temp_Emp 
    
    ---Requirement
    
    Select '100AS' as COl1,'12U' as COl2 union
    Select '189722' as COl1,'' as COl2 union
    Select '123' as COl1,'yure12' as COl2 union
    Select 'qswer' as COl1,'23' as COl2
    
    
    
    SELECT distinct  S.a.value('(/H/r)[1]', 'VARCHAR(100)') AS COL1, ISNULL( S.a.value('(/H/r)[2]', 'VARCHAR(100)'),'')  AS COL2
    FROM
    (
    SELECT *,CAST (N'<H><r>' + REPLACE(EmpID, '-', '</r><r>') + '</r></H>' AS XML) AS [vals]
    FROM #Temp_Emp) d
     
    CROSS APPLY d.[vals].nodes('/H/r') S(a)
    
    Drop table #Temp_Emp

    • Marked as answer by Manesar Tuesday, August 12, 2014 8:11 PM
    Tuesday, August 12, 2014 2:55 PM
  • Try this:

    ;WITH CTE_Split (EmpID, xmlEmpID)
    AS
    (
        SELECT EmpID,
        CONVERT(XML,'<Names><name>'+ REPLACE(EmpID,'-', '</name><name>') + '</name></Names>') AS xmlEmpID
          FROM #Temp_Emp
    )
    
     SELECT   
     xmlEmpID.value('/Names[1]/name[1]','varchar(100)') AS COL1,    
     ISNULL(xmlEmpID.value('/Names[1]/name[2]','varchar(100)'),'') AS COL2
     FROM CTE_Split

    Reference- here.


    If this post answers your query, please click "Mark As Answer" or "Vote as Helpful".

    • Marked as answer by Manesar Tuesday, August 12, 2014 8:11 PM
    Tuesday, August 12, 2014 2:57 PM
  • SELECT CASE WHEN EMPID LIKE '%-%' THEN LEFT(EMPID,CHARINDEX('-',EMPID)-1) ELSE EMPID END AS COL1
    , CASE WHEN EMPID LIKE '%-%' THEN RIGHT(EMPID,CHARINDEX('-',REVERSE(EMPID))-1) ELSE NULL END AS COL2
    FROM #TEMP_EMP
    

    Thanks.
    • Marked as answer by Manesar Tuesday, August 12, 2014 8:11 PM
    Tuesday, August 12, 2014 3:19 PM
  • Can you please try this,

     ;WITH CTE (EmpID, xmlID)
    AS
    (
        SELECT Empid,
        CONVERT(XML,'<Names><name>'  
        + REPLACE(Empid,'-', '</name><name>') + '</name></Names>') AS xmlID
          FROM #Temp_Emp
    )
    
     SELECT       
     xmlID.value('/Names[1]/name[1]','varchar(100)') AS Col1,    
     Isnull(xmlID.value('/Names[1]/name[2]','varchar(100)'),'') AS Col2
     FROM CTE

    Please Mark This As Answer if it helps to solve the issue


    • Edited by VINO-TH Tuesday, August 12, 2014 4:37 PM Link update
    • Marked as answer by Manesar Tuesday, August 12, 2014 8:11 PM
    Tuesday, August 12, 2014 4:34 PM
  • Create table #Temp_Emp (EmpID varchar(30))
    go
    
    insert into #Temp_Emp  values ('100AS-12U')
    insert into #Temp_Emp  values ('189722')
    insert into #Temp_Emp  values ('123-yure12')
    insert into #Temp_Emp  values ('qswer-23')
    
    
    Select a.EmpID,Case when CHARINDEX('-',a.EmpID,1) > 0 then Left(a.EmpID,CHARINDEX('-',a.EmpID,1)-1) else a.EmpID end as Col1, Case when CHARINDEX('-',a.EmpID,1) > 0 then SUBSTRING(a.EmpID,CHARINDEX('-',a.EmpID,1)+1, Len(a.EmpID)) else '' end as Col2
    from #Temp_Emp a


    Please visit my Blog for some easy and often used t-sql scripts
    My BizCard

    • Marked as answer by Manesar Tuesday, August 12, 2014 8:11 PM
    Tuesday, August 12, 2014 4:35 PM

All replies

  • Can you try the below code?

    Create table #Temp_Emp (EmpID varchar(30))
    go
    
    insert into #Temp_Emp  values ('100AS-12U')
    insert into #Temp_Emp  values ('189722')
    insert into #Temp_Emp  values ('123-yure12')
    insert into #Temp_Emp  values ('qswer-23')
    
    select PARSENAME(replace(empid,'-','.'),2) col1,PARSENAME(replace(empid,'-','.'),1)col2 from #Temp_Emp
    --Prashanth

    • Marked as answer by Manesar Tuesday, August 12, 2014 8:10 PM
    • Unmarked as answer by Manesar Tuesday, August 12, 2014 8:10 PM
    • Marked as answer by Manesar Tuesday, August 12, 2014 8:10 PM
    Tuesday, August 12, 2014 2:31 PM
  • Create table #Temp_Emp (EmpID varchar(30))
    go
    
    insert into #Temp_Emp  values ('100AS-12U')
    insert into #Temp_Emp  values ('189722')
    insert into #Temp_Emp  values ('123-yure12')
    insert into #Temp_Emp  values ('qswer-23')
    
    --select * from #Temp_Emp 
    
    ---Requirement
    
    Select '100AS' as COl1,'12U' as COl2 union
    Select '189722' as COl1,'' as COl2 union
    Select '123' as COl1,'yure12' as COl2 union
    Select 'qswer' as COl1,'23' as COl2
    
    
    
    SELECT distinct  S.a.value('(/H/r)[1]', 'VARCHAR(100)') AS COL1, ISNULL( S.a.value('(/H/r)[2]', 'VARCHAR(100)'),'')  AS COL2
    FROM
    (
    SELECT *,CAST (N'<H><r>' + REPLACE(EmpID, '-', '</r><r>') + '</r></H>' AS XML) AS [vals]
    FROM #Temp_Emp) d
     
    CROSS APPLY d.[vals].nodes('/H/r') S(a)
    
    Drop table #Temp_Emp

    • Marked as answer by Manesar Tuesday, August 12, 2014 8:11 PM
    Tuesday, August 12, 2014 2:55 PM
  • Try this:

    ;WITH CTE_Split (EmpID, xmlEmpID)
    AS
    (
        SELECT EmpID,
        CONVERT(XML,'<Names><name>'+ REPLACE(EmpID,'-', '</name><name>') + '</name></Names>') AS xmlEmpID
          FROM #Temp_Emp
    )
    
     SELECT   
     xmlEmpID.value('/Names[1]/name[1]','varchar(100)') AS COL1,    
     ISNULL(xmlEmpID.value('/Names[1]/name[2]','varchar(100)'),'') AS COL2
     FROM CTE_Split

    Reference- here.


    If this post answers your query, please click "Mark As Answer" or "Vote as Helpful".

    • Marked as answer by Manesar Tuesday, August 12, 2014 8:11 PM
    Tuesday, August 12, 2014 2:57 PM
  • SELECT CASE WHEN EMPID LIKE '%-%' THEN LEFT(EMPID,CHARINDEX('-',EMPID)-1) ELSE EMPID END AS COL1
    , CASE WHEN EMPID LIKE '%-%' THEN RIGHT(EMPID,CHARINDEX('-',REVERSE(EMPID))-1) ELSE NULL END AS COL2
    FROM #TEMP_EMP
    

    Thanks.
    • Marked as answer by Manesar Tuesday, August 12, 2014 8:11 PM
    Tuesday, August 12, 2014 3:19 PM
  • Can you please try this,

     ;WITH CTE (EmpID, xmlID)
    AS
    (
        SELECT Empid,
        CONVERT(XML,'<Names><name>'  
        + REPLACE(Empid,'-', '</name><name>') + '</name></Names>') AS xmlID
          FROM #Temp_Emp
    )
    
     SELECT       
     xmlID.value('/Names[1]/name[1]','varchar(100)') AS Col1,    
     Isnull(xmlID.value('/Names[1]/name[2]','varchar(100)'),'') AS Col2
     FROM CTE

    Please Mark This As Answer if it helps to solve the issue


    • Edited by VINO-TH Tuesday, August 12, 2014 4:37 PM Link update
    • Marked as answer by Manesar Tuesday, August 12, 2014 8:11 PM
    Tuesday, August 12, 2014 4:34 PM
  • Create table #Temp_Emp (EmpID varchar(30))
    go
    
    insert into #Temp_Emp  values ('100AS-12U')
    insert into #Temp_Emp  values ('189722')
    insert into #Temp_Emp  values ('123-yure12')
    insert into #Temp_Emp  values ('qswer-23')
    
    
    Select a.EmpID,Case when CHARINDEX('-',a.EmpID,1) > 0 then Left(a.EmpID,CHARINDEX('-',a.EmpID,1)-1) else a.EmpID end as Col1, Case when CHARINDEX('-',a.EmpID,1) > 0 then SUBSTRING(a.EmpID,CHARINDEX('-',a.EmpID,1)+1, Len(a.EmpID)) else '' end as Col2
    from #Temp_Emp a


    Please visit my Blog for some easy and often used t-sql scripts
    My BizCard

    • Marked as answer by Manesar Tuesday, August 12, 2014 8:11 PM
    Tuesday, August 12, 2014 4:35 PM
  • thanks Johnny!

    Thanks everyone!

    Tuesday, August 12, 2014 8:10 PM