SQL select case when and retrieve level2 and level3 from string

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.
LVL 1
LD16Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Organization format is always as you posted? 5 levels with a dot separating each level?
0
Scott PletcherSenior DBACommented:
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
0
LD16Author 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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Vitor MontalvãoMSSQL Senior EngineerCommented:
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).
0
LD16Author Commented:
Eveey time you have more than 3.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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

0
Scott PletcherSenior DBACommented:
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
0
awking00Commented:
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;
0
LD16Author Commented:
Thank you both. I will test the two queries tomorrow.
0
awking00Commented:
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;
0
LD16Author 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.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Did you try mine solution?
0
LD16Author 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
0
LD16Author 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;
0
LD16Author 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
0
awking00Commented:
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
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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

0
LD16Author 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.
0
awking00Commented:
What are all of the datatypes of your select and join fields?
0
LD16Author Commented:
@Vitor I have some corrections however I still having some inconsistencies ex:
Line 8 and 9

Capture.GIF
0
LD16Author Commented:
@awking00: all are nvarchar (255)
0
Scott PletcherSenior DBACommented:
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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
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

0
Vitor MontalvãoMSSQL Senior EngineerCommented:
My last script didn't work?
0
LD16Author 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?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
LD16, do you want to review the points for this question?
I can enable it for you if you want.
0
LD16Author Commented:
Yes, please.
0
LD16Author 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.
0
Scott PletcherSenior DBACommented:
SELECT ISNULL(NULLIF(s.[Owning Organization], ''), m.[Owning Organization]) --AS owning_organization
0
LD16Author Commented:
Perfect, thank you!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.