CODEWITHSUNDEEP
sqlsql-serversubstring
Akansha
Akansha

Reputation: 209

Dynamic Substring in SQL

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

#temp1:

enter image description here

#temp2:

enter image description here

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:

enter image description here

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?!

Upvotes: 1

Views: 3517

Answers (2)

Andrej Ivanovič
Andrej Ivanovič

Reputation: 21

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

Upvotes: 0

Eric Brandt
Eric Brandt

Reputation: 8101

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

Upvotes: 1

Related Questions