We help IT Professionals succeed at work.

SQL select case when and retrieve level2 and level3 from string

Luis Diaz
Luis Diaz asked
on
Hello Experts,

I have the following query select id, organization1, organization2 from project

And the corresponding data

Id; organization1;organization2
5858;level1.level2.level3.level4.level5;
58968;; titi1.titi2.titi3.titi4.titi5

As you can see sometimes organization2 doesn’t have data the same for organization1

I need to perform a sql query that does the following:

Select case when Organization1 is not null take into account Organization 1 else take into Organization2 as OrganizationRevised. If both are not null take into account Organization 1

Select just level2.level3 of OrganizationRevised

Final result

Id Organization Revised
5858;level2.level3
58968;titi2.titi3

Thank you for your help.
Comment
Watch Question

Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Organization format is always as you posted? 5 levels with a dot separating each level?
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
SELECT id, ISNULL(PARSENAME(org_3_levels, 2) + '.', '') + ISNULL(PARSENAME(org_3_levels, 1), '') AS [Organization Revised]
FROM ( --replace "test_data" with your table name after "FROM"
    SELECT 5858 AS id, 'level1.level2.level3.level4.level5;' AS organization1, '' AS organization2 UNION ALL
    SELECT 58968, '', 'titi1.titi2.titi3.titi4.titi5'
) AS test_data
CROSS APPLY (
    SELECT ISNULL(NULLIF(organization1, ''), organization2) AS org_all_levels
) AS ca1
CROSS APPLY (
    SELECT LEFT(org_all_levels, CHARINDEX('.', org_all_levels, CHARINDEX('.', org_all_levels, CHARINDEX('.', org_all_levels) + 1) + 1) - 1) AS org_3_levels
) AS ca2
Luis DiazIT consultant

Author

Commented:
@Victor: Organization can have more than 5 levels. The idea is to retrieve just level2 and level3.
@ScottPletcher: the clause:
 SELECT 5858 AS id, 'level1.level2.level3.level4.level5;' AS organization1, '' AS organization2 UNION ALL
    SELECT 58968, '', 'titi1.titi2.titi3.titi4.titi5' will be complicated to manage as I have more than 500 lines in my table so I cannot add a select for each line. In my example I just putted two line to show how the table works.

Thank you again.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Organization can have more than 5 levels. The idea is to retrieve just level2 and level3.
And can have less than 3 levels? Just want to know for handling the exception cases (1 or 2 levels only).
Luis DiazIT consultant

Author

Commented:
Eveey time you have more than 3.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Ok. Then try this script:
;WITH CTE_ORG (Id, Org, Pos)
AS
	(SELECT Id, ISNULL(organization1, organization2), CHARINDEX('.',ISNULL(organization1, organization2))
       FROM Project)
	
SELECT Id, LEFT(SUBSTRING(Org,Pos+1,LEN(Org)),CHARINDEX('.',Org,CHARINDEX('.',Org)+1)-1) OrganizationRevised
FROM CTE_ORG

Open in new window

Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
The SELECTs with data were just to provide test data.  For your actual query, use your actual table name instead, like so:


SELECT id, ISNULL(PARSENAME(org_3_levels, 2) + '.', '') + ISNULL(PARSENAME(org_3_levels, 1), '') AS [Organization Revised]
 FROM [your_table_name]
 CROSS APPLY (
     SELECT ISNULL(NULLIF(organization1, ''), organization2) AS org_all_levels
 ) AS ca1
 CROSS APPLY (
     SELECT LEFT(org_all_levels, CHARINDEX('.', org_all_levels, CHARINDEX('.', org_all_levels, CHARINDEX('.', org_all_levels) + 1) + 1) - 1) AS org_3_levels
 ) AS ca2
awking00Information Technology Specialist

Commented:
with cte as
(select id,
 substring(coalesce(org1, org2), charindex('.',coalesce(org1, org2) + 1, len(coalesce(org1, org2)))) as neworg
 from project)
select id, left(neworg,charindex('.',neworg) as orgrevised
from cte;
Luis DiazIT consultant

Author

Commented:
Thank you both. I will test the two queries tomorrow.
awking00Information Technology Specialist

Commented:
Missed a part -
with cte as
(select id,
 substring(coalesce(org1, org2), charindex('.',coalesce(org1, org2) + 1, len(coalesce(org1, org2)))) as neworg
 from project)
select id, left(neworg,charindex('.',neworg) -1) as orgrevised
from cte;
Luis DiazIT consultant

Author

Commented:
@ScottPletcher I got the following error message:

  Server: Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring or left function.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Did you try mine solution?
Luis DiazIT consultant

Author

Commented:
@ScottPletcher  Here is my query with the revised data, thank you for your help:

SELECT [Cardinality],sm.[SPoT Internal ID],[SPOT ID],m.[ID], s.[Owning Organization] as OwningOrganizationS,m.[Owning Organization] as OwningOrganizationM, ISNULL(PARSENAME(org_3_levels, 2) + '.', '') + ISNULL(PARSENAME(org_3_levels, 1), '') AS [Organization Revised]FROM [ods].[IMP_CSV_SPOT_MSTT] sm
left join [ods].[IMP_CSV_SPOT] s on s.[ID] =sm.[SPOT ID]
left join [ods].[IMP_CSV_MSTT] m on m.[ID] =sm.[MSTT ID]
 CROSS APPLY (
     SELECT ISNULL(NULLIF(s.[Owning Organization], ''), m.[Owning Organization]) AS org_all_levels
 ) AS ca1
 CROSS APPLY (
     SELECT LEFT(org_all_levels, CHARINDEX('.', org_all_levels, CHARINDEX('.', org_all_levels, CHARINDEX('.', org_all_levels) + 1) + 1) - 1) AS org_3_levels
 ) AS ca2
Luis DiazIT consultant

Author

Commented:
@awking00 I got the following error message: substring fonction required 3 arguments with the following query:

 with cte as
(select [Cardinality],sm.[SPoT Internal ID],[SPOT ID],m.[ID],substring(coalesce(s.[Owning Organization], m.[Owning Organization]), charindex('.',coalesce(s.[Owning Organization], m.[Owning Organization]) + 1, len(coalesce(s.[Owning Organization], m.[Owning Organization])))) as neworg
 from [ods].[IMP_CSV_SPOT_MSTT] sm
left join [ods].[IMP_CSV_SPOT] s on s.[ID] =sm.[SPOT ID]
left join [ods].[IMP_CSV_MSTT] m on m.[ID] =sm.[MSTT ID])
select id, left(neworg,charindex('.',neworg) -1) as orgrevised
from cte;
Luis DiazIT consultant

Author

Commented:
@Victor: I tried your solution however I get in Organization revised level2 & just the first letter of level 3 and I need to have all the string of level2 and level 3
Capture.GIF
awking00Information Technology Specialist

Commented:
Sorry, I misplaced the parentheses. One should go after the "+ 1" and one should be removed from the end of the substring -
substring(coalesce(s.[Owning Organization], m.[Owning Organization]), charindex('.',coalesce(s.[Owning Organization], m.[Owning Organization]) + 1), len(coalesce(s.[Owning Organization], m.[Owning Organization]))) as neworg
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
It's the problem with not working with real data so we can check all possibilities. So, the previous version fails on data with 3 levels only. This new version is verifying that:
;WITH CTE_ORG (Id, Org, Pos)
AS
	(SELECT Id, ISNULL(organization1, organization2), CHARINDEX('.',ISNULL(organization1, organization2))
       FROM Project)
	
SELECT Id, CASE CHARINDEX('.',Org,CHARINDEX('.',Org,Pos+1)+1)
	    WHEN 0 THEN SUBSTRING(Org,Pos+1,LEN(Org))
            ELSE LEFT(SUBSTRING(Org,Pos+1,LEN(Org)),CHARINDEX('.',Org,CHARINDEX('.',Org)+1)-1) 
           END OrganizationRevised
FROM CTE_ORG

Open in new window

Luis DiazIT consultant

Author

Commented:
@awking00: I made the notification but now I got an error message:
Error converting datatype nvarchar to int

Thank you in advance for your help.
awking00Information Technology Specialist

Commented:
What are all of the datatypes of your select and join fields?
Luis DiazIT consultant

Author

Commented:
@Vitor I have some corrections however I still having some inconsistencies ex:
Line 8 and 9

Capture.GIF
Luis DiazIT consultant

Author

Commented:
@awking00: all are nvarchar (255)
Senior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
SELECT [Cardinality],sm.[SPoT Internal ID],[SPOT ID],m.[ID], s.[Owning Organization] as OwningOrganizationS,m.[Owning Organization] as OwningOrganizationM, ISNULL(PARSENAME(org_3_levels, 2) + '.', '') + ISNULL(PARSENAME(org_3_levels, 1), '') AS [Organization Revised]FROM [ods].[IMP_CSV_SPOT_MSTT] sm
 left join [ods].[IMP_CSV_SPOT] s on s.[ID] =sm.[SPOT ID]
 left join [ods].[IMP_CSV_MSTT] m on m.[ID] =sm.[MSTT ID]
  CROSS APPLY (
      SELECT ISNULL(NULLIF(s.[Owning Organization], ''), m.[Owning Organization]) AS org_all_levels
  ) AS ca1
  CROSS APPLY (
      SELECT LEFT(org_all_levels, CHARINDEX('.', org_all_levels + '.', CHARINDEX('.', org_all_levels, CHARINDEX('.', org_all_levels) + 1) + 1) - 1) AS org_3_levels
  ) AS ca2
Vitor MontalvãoIT Engineer
Distinguished Expert 2017
Commented:
Please try this new version:
;WITH CTE_ORG (Id, Org, Pos)
AS
	(SELECT Id, ISNULL(organization1, organization2), CHARINDEX('.',ISNULL(organization1, organization2))
       FROM Project)
	
SELECT Id, CASE CHARINDEX('.',Org,CHARINDEX('.',Org,Pos+1)+1)
	    WHEN 0 THEN SUBSTRING(Org,Pos+1,LEN(Org))
            ELSE SUBSTRING(Org,Pos+1,CHARINDEX('.',Org,CHARINDEX('.',Org,Pos+1)+1)-Pos-1)
           END OrganizationRevised
FROM CTE_ORG

Open in new window

Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
My last script didn't work?
Luis DiazIT consultant

Author

Commented:
Sorry, I just tested the query of ScottPletcher, I have now tested your query and it works.
@ScottPletcher coud you please reopen the question?
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
LD16, do you want to review the points for this question?
I can enable it for you if you want.
Luis DiazIT consultant

Author

Commented:
Yes, please.
Luis DiazIT consultant

Author

Commented:
Hello,

Can someone help me with this revised spec:

I need the same query without retrieving level 2 & level 3.
I just need the following:
if s.[Owning Organization] is not null take s.[Owning Organization]  else
m.[Owning Organization]

Thank you.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
SELECT ISNULL(NULLIF(s.[Owning Organization], ''), m.[Owning Organization]) --AS owning_organization
Luis DiazIT consultant

Author

Commented:
Perfect, thank you!