Answered by:
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?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 #employeetypeBest 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 AMAnswerer -
Hi,
select parsename(replace(EmployeeType,'-','.'),1)
from #employeetype
Mark as answer if it helps. Thanks.- Edited by Soumen Barua Tuesday, August 6, 2019 5:47 AM
- Marked as answer by Revathy Menon Tuesday, August 6, 2019 2:52 PM
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 #employeetypeBest 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 AMAnswerer -
Hi,
select parsename(replace(EmployeeType,'-','.'),1)
from #employeetype
Mark as answer if it helps. Thanks.- Edited by Soumen Barua Tuesday, August 6, 2019 5:47 AM
- Marked as answer by Revathy Menon Tuesday, August 6, 2019 2:52 PM
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