locked
Substring based on few conditions RRS feed

  • Question

  • Hi,
    I have a column 'EmployeeType' in a table , which has sample values like

    IND-123-SoftwareEngineer
    SRI-123459-Manager
    Carpenter
    HR
    USA-1001-ProjectManager
    CAN-000000001-Doctor

    from this, I want the output as

    SoftwareEngineer
    Manager
    Carpenter
    HR
    ProjectManager
    Doctor

    So, the logic is,

    if the record has -(hyphen), then substring after two hyphens &

    if the record does not have - (hyphen), then just use the value as is

      Below is the script i created to try this - but not able to achieve

    Create table #employeetype  (EmployeeType varchar (50))
    insert into #employeetype values 
    ('IND-123-SoftwareEngineer'),
    ('SRI-123459-Manager'),
    ('Carpenter'),
    ('HR'),
    ('USA-1001-ProjectManager')
    ,('CAN-000000001-Doctor')

    select * from #employeetype

    Can anyone please help me on this?

    Tuesday, August 6, 2019 5:16 AM

Answers

  • select * ,case when  charindex('-',reverse(EmployeeType))>0
    then right(EmployeeType,charindex('-',reverse(EmployeeType))-1) else EmployeeType end 
    from #employeetype

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Marked as answer by Revathy Menon Tuesday, August 6, 2019 2:52 PM
    Tuesday, August 6, 2019 5:22 AM
    Answerer
  • Hi,

    select parsename(replace(EmployeeType,'-','.'),1)
    from #employeetype

    Mark as answer if it helps. Thanks.
    Tuesday, August 6, 2019 5:47 AM
  • Hi Revathy,

    Try following code and it will solve your requirement

    select *,SUBSTRING(EmployeeType,CHARINDEX('-',EmployeeType,(charindex('-',EmployeeType)+1))+1,len(EmployeeType))
    from #employeetype


    Thanks

    Srinivasa Rao G, MCSE(Business Intelligence) Blog: TechTalksPro

    Please mark as answer if my post is helped to solve your problem
    and vote as helpful if it helped so that forum users can benefit

    • Marked as answer by Revathy Menon Tuesday, August 6, 2019 2:52 PM
    Tuesday, August 6, 2019 6:15 AM
  • Hi Revathy Menon,

     

    Please try following script.

     
    select *,
    stuff(EmployeeType,1,CHARINDEX('-',EmployeeType,(charindex('-',EmployeeType)+1)),'') result
    from #employeetype
    /*
    EmployeeType                                       result
    -------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    IND-123-SoftwareEngineer                           SoftwareEngineer
    SRI-123459-Manager                                 Manager
    Carpenter                                          Carpenter
    HR                                                 HR
    USA-1001-ProjectManager                            ProjectManager
    CAN-000000001-Doctor                               Doctor
    */
    

    Hope it will help you.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Revathy Menon Tuesday, August 6, 2019 2:52 PM
    Tuesday, August 6, 2019 6:36 AM

All replies

  • select * ,case when  charindex('-',reverse(EmployeeType))>0
    then right(EmployeeType,charindex('-',reverse(EmployeeType))-1) else EmployeeType end 
    from #employeetype

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Marked as answer by Revathy Menon Tuesday, August 6, 2019 2:52 PM
    Tuesday, August 6, 2019 5:22 AM
    Answerer
  • Hi,

    select parsename(replace(EmployeeType,'-','.'),1)
    from #employeetype

    Mark as answer if it helps. Thanks.
    Tuesday, August 6, 2019 5:47 AM
  • Hi Revathy,

    Try following code and it will solve your requirement

    select *,SUBSTRING(EmployeeType,CHARINDEX('-',EmployeeType,(charindex('-',EmployeeType)+1))+1,len(EmployeeType))
    from #employeetype


    Thanks

    Srinivasa Rao G, MCSE(Business Intelligence) Blog: TechTalksPro

    Please mark as answer if my post is helped to solve your problem
    and vote as helpful if it helped so that forum users can benefit

    • Marked as answer by Revathy Menon Tuesday, August 6, 2019 2:52 PM
    Tuesday, August 6, 2019 6:15 AM
  • Hi Revathy Menon,

     

    Please try following script.

     
    select *,
    stuff(EmployeeType,1,CHARINDEX('-',EmployeeType,(charindex('-',EmployeeType)+1)),'') result
    from #employeetype
    /*
    EmployeeType                                       result
    -------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    IND-123-SoftwareEngineer                           SoftwareEngineer
    SRI-123459-Manager                                 Manager
    Carpenter                                          Carpenter
    HR                                                 HR
    USA-1001-ProjectManager                            ProjectManager
    CAN-000000001-Doctor                               Doctor
    */
    

    Hope it will help you.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Revathy Menon Tuesday, August 6, 2019 2:52 PM
    Tuesday, August 6, 2019 6:36 AM
  • Create table #employeetype  (EmployeeType varchar (50))
    insert into #employeetype values 
    ('IND-123-SoftwareEngineer'),
    ('SRI-123459-Manager'),
    ('Carpenter'),
    ('HR'),
    ('USA-1001-ProjectManager')
    ,('CAN-000000001-Doctor')
    
    ;with mycte as (
    select * ,  
    CAST (N'<H><r>' +  REPLACE(EmployeeType,'-',  '</r><r>')+ '</r></H>' AS XML) vals
    from #employeetype
      )
    , mycte1 as
     ( 
     SELECT  DISTINCT EmployeeType,  S.a.value('(/H/r)[1]', 'NVARCHAR(50)') AS c1,
    S.a.value('(/H/r)[2]', 'NVARCHAR(50)') AS c2,
    S.a.value('(/H/r)[3]', 'NVARCHAR(50)') AS c3 
    --,S.a.value('(/H/r)[4]', 'NVARCHAR(50)') AS c4,
    --S.a.value('(/H/r)[5]', 'NVARCHAR(50)') AS c5 
     
    FROM mycte d
      
    CROSS APPLY d.[vals].nodes('/H/r') S(a)
    )
     Select * from mycte1
     
    
    drop  table #employeetype 

    Tuesday, August 6, 2019 3:55 PM