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.
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
WHEN (INSTR(g.CallCounter,':',1,19)) ='BYE' THEN
('BYE message received')
END AS NewField
I have to write for 5 different scenarios.
It is not working.
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 ...
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 for the replies.
It worked with the solution,
What does for 5 indicate?
If it is the 20th field and for two scenarios,is it
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.
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.