Case statement

General
Fan

Case statement

I need a help with a functionality.

In one of the fields, the data is as follows:

The field name is CallCounter. The content inside the field is seperated by semicolon.

SIP;1594607026_59172034@155.172.67.4;<sip:+19365247859@155.172.67.4:5060~user=phone>~tag=gK0b5b7fc0;<sip:2103698342@12.95.110.74:5060~user=phone>~tag=SD4os6399-747866718;0;;;;sip:+12103698342~rn=+12105707985~tgrp=nycmny83ce1awl1s~trunk-context=dsipic.att.net@12.95.110.74:5060~user=phone;+19365247859@155.172.67.4;;;sip:+19365247859@155.172.67.4:5060;sip:+12103698342~rn=+12105707985@12.95.110.74:5060~transport=udp;;;;;BYE;16;0;0;;0;0;;63b35da0-52fa-1000-00-00-00-10-6b-01-3f-d0;155.172.67.4;0314;;;;1;0;0;0;;;;

I am interested in the the 19th. In this case, it is BYE.Similarly there are 5 different values.

I want a function that can identify the value (in this case BYE) and if so, then I can have a value of “BYE message received”

I tried like this

Select Top 1000 g.CallCounter

CASE

WHEN (INSTR(g.CallCounter,':',1,19))  ='BYE'  THEN

('BYE message received')

END AS NewField

FROM…….

I have to write for 5 different scenarios.

It is not working.

Any ideas?

Thanks,

pragov

4 REPLIES
Junior Contributor

Re: Case statement

Hi Pragov,

it would be helpful to know what you mean by "It is not working"

There's probably an error message as INSTR returns the position where it found the 19th ';' and you compare it to 'BYE'.

And within INSTR you use ':' instead of ';'

You might use a SUBSTRING or a brute-force LIKE

case when substring(CallCounter from INSTR(CallCounter,';',1,18) for 5) =';BYE;' then ...

case when CallCounter like '%;%;%;%;%;%;%;%;%;%;%;%;%;%;%;%;%;%;Bye;%' then ...
Enthusiast

Re: Case statement

Hi Pragov, 

Have you considered loading this data into a table using the semi-colon as a column delimiter, which makes your query much simpler.  Your spool will definitely drop and your performance will increase dramatically if you partition by the colum you're interested in, "BYE".

Rather than than struggle to write a query that probably won't perform as the end user would like it to, make  it as simple as possible.

Thanks,

B

Fan

Re: Case statement

Hi Dieter/B,

Thanks for the replies.

It worked with the solution,

case when substring(CallCounter from INSTR(CallCounter,';',1,18) for 5) =';BYE;' then ...

What does for 5 indicate?

If it is the 20th field and for two scenarios,is it

case when substring(CallCounter from INSTR(CallCounter,';',1,19) for 5) =';OTHER;' then ...???

Hi B...As for the second method,I cannot dump into a table,since this field is among the 25 other fields and as well as other issues with the local DBA structures.

Junior Contributor

Re: Case statement

Hi Pragov, 

you should know the functionality of basic functions like SUBSTRING, 5 indicates the number of characters returned, i.e. ';BYE;'. If you want to compare to ';OTHER;' you need to extract 7 characters.