SQL select case when and retrieve level2 and level3 from string

Organization format is always as you posted? 5 levels with a dot separating each level?

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

@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.

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).

Eveey time you have more than 3.

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

Select allOpen in new window

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

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;

Thank you both. I will test the two queries tomorrow.

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;

@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.

Did you try mine solution?

@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

@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;

@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

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

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

Select allOpen in new window

@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.

What are all of the datatypes of your select and join fields?

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

@awking00: all are nvarchar (255)

My last script didn't work?

Sorry, I just tested the query of ScottPletcher, I have now tested your query and it works.
@ScottPletcher coud you please reopen the question?

LD16, do you want to review the points for this question?
I can enable it for you if you want.

Yes, please.

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.

Perfect, thank you!