Substring based on few conditions

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?

Answer 1

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

Answer 2

Hi,

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

Mark as answer if it helps. Thanks.

Answer 3

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

Answer 4

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 [e-mail].

Answer 5

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