Dynamic Substring in SQL

Question

I am working on a project where I have the two below tables:

#temp1:

#temp2:

So for every client on #temp2 table, I have to check if the substring(outcome,1,12) matches with text1/text2/text3/text4 of #temp1 table. And if they match the then concatenate text1/text2/text3/text4(replace the matching substring on #temp1 with outcome of #temp2). The final output is to be as below:

#output desired:

So for CID 1001, the substring(#temp2.outcome,1,12) = SOP201800205 matches with #temp1.text2 = SOP201800205. So my output should be

   Concat(#temp1.text1,#temp2.outcome)

My trial here in rextester demo:

https://rextester.com/CAEP69354

Any help?!

You are getting some error ? or what? Is it not clear in your question. — Gaurav
– Gaurav, Commented Dec 6, 2018 at 15:31
I am not getting any error. I'm just unable to complete my code. You cna check the link provided — Akansha
– Akansha, Commented Dec 6, 2018 at 15:33
I don't understand at all what you are trying to do here. Could you make your sample data a bit easier to decipher? 20+ character strings of random characters is really hard to follow. Can you also try to explain what the logic is here? I can't understand how those two tables relate. It does seem that the biggest issue is your first table is denormalized and violates 1NF with repeating columns. — Sean Lange
– Sean Lange, Commented Dec 6, 2018 at 15:50
@SeanLange Pls chk this link - rextester.com/QFI78492 Simplified the test data — Akansha
– Akansha, Commented Dec 6, 2018 at 17:29

Eric Brandt · Accepted Answer · 2018-12-06 15:55:57Z

1

If I understand your question correctly, you're really trying to find out if any of the values in #temp1 are completely contained in the outcome of #temp2. Despite your SUBSTRING parameters, I think you don't care how long the strings are, so I used the length of the #temp1 value to set the parameter for the #temp2 SUBSTRING.

If that's correct, then you just need a more robust CASE statement. This should work for you:

select
    t1.cid,
    case 
        when t1.text1 = substring(t2.outcome,1,len(t1.text1))
            then concat(t2.outcome,' ',t1.text2,' ',t1.text3,' ',t1.text4)
        when t1.text2 = substring(t2.outcome,1,len(t1.text2))
            then concat(t1.text1,' ',t2.outcome, ' ',t1.text3,' ',t1.text4)
        when t1.text3 = substring(t2.outcome,1,len(t1.text3))
            then concat(t1.text1,' ',t1.text2, ' ',t2.outcome,' ',t1.text4)
        when t1.text4 = substring(t2.outcome,1,len(t1.text4))
            then concat(t1.text1,' ',t1.text2, ' ',t1.text3,' ',t2.outcome)
        else concat(t1.text1,' ',t1.text2, ' ',t1.text3,' ',t1.text4)
    end
from #temp1 t1
join #temp2 t2 on t1.cid = t2.cid;

Rextester: https://rextester.com/BIR87919

answered Dec 6, 2018 at 15:55

Eric Brandt

8,1313 gold badges20 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Akansha Over a year ago

Eric, this helped me. Sorry about the delay. But yes, I want to compare if the whole of #temp1 is in the outcome and concatenate with the rest of the data.

Eric Brandt Over a year ago

As long as it helped, time doesn't matter. :) Thanks!

Andrej Ivanovič · Accepted Answer · 2018-12-06 16:03:26Z

You have only one case where substring of text1 or text2 or text3 or text4
is equal to t2.outcome of CID

select
t1.cid,
substring(t1.text1,1,12),
substring(t1.text2,1,12),
substring(t1.text3,1,12),
substring(t1.text4,1,12),
substring(t2.outcome,1,12) as outcome,
case when substring(t1.text1,1,12) = substring(t2.outcome,1,12) then 'text1 = outcome'
end,
case 
     when substring(t1.text2,1,12) = substring(t2.outcome,1,12) then 'text2 = outcome'

end,
case when substring(t1.text3,1,12) = substring(t2.outcome,1,12) then 'text3 = outcome'
end,
case when substring(t1.text4,1,12) = substring(t2.outcome,1,12) then 'text4 = outcome'
end

from #temp1 t1
join #temp2 t2 on t1.cid = t2.cid

I think you have wrong substring do you expect substring(t1.text2,1,12) = substring(t2.outcome,1,12) or substring(t1.text2,1,11) = substring(t2.outcome,1,11) do you relay want to check substring substring(t1.text,1,12) ?

Collectives™ on Stack Overflow

Dynamic Substring in SQL

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related