June 22, 2018 at 11:38 am
Greetings,
I am not highly proficient in SQL as I'd like to be - I can get by but this latest problem has me submitting my first request for help.I'm trying to locate specific text in a column for each record. The column is defined as NVARCHAR(MAX) and is not a fixed length or delimited structure (I should be so lucky).
The text is related to Interactive Voice Response (IVR) choices i.e. person selects option 3 for billing, then option 2 etc etc. The text can be repeated multiple times in that record (I think up to 10 responses).
I have been able to pull info for the first occurrence using the following:
, CASE WHEN SUBSTRING(CDR,CHARINDEX('Routing call IVR =',CDR),18) = 'Routing call IVR =' THEN SUBSTRING(CDR,CHARINDEX('Routing call IVR =',CDR)+19,7)
ELSE ''
END AS 'IVRQueue1'
The result is: IVR5001 (which is the queue or option the person selected). I'm trying to put the results into its own column i.e. IVRQueue1, IVRQueue2, IVRQueue3......
I've tried using REVERSE to work from the end of the record etc but haven't found the correct combination of functions.
Has anyone else worked with similar CDR data and possibly point me to some examples?
Thanks in advance, Paul
June 22, 2018 at 12:32 pm
SonicG - Friday, June 22, 2018 11:38 AMGreetings,
I am not highly proficient in SQL as I'd like to be - I can get by but this latest problem has me submitting my first request for help.I'm trying to locate specific text in a column for each record. The column is defined as NVARCHAR(MAX) and is not a fixed length or delimited structure (I should be so lucky).The text is related to Interactive Voice Response (IVR) choices i.e. person selects option 3 for billing, then option 2 etc etc. The text can be repeated multiple times in that record (I think up to 10 responses).
I have been able to pull info for the first occurrence using the following:
, CASE WHEN SUBSTRING(CDR,CHARINDEX('Routing call IVR =',CDR),18) = 'Routing call IVR =' THEN SUBSTRING(CDR,CHARINDEX('Routing call IVR =',CDR)+19,7)
ELSE ''
END AS 'IVRQueue1'The result is: IVR5001 (which is the queue or option the person selected). I'm trying to put the results into its own column i.e. IVRQueue1, IVRQueue2, IVRQueue3......
I've tried using REVERSE to work from the end of the record etc but haven't found the correct combination of functions.
Has anyone else worked with similar CDR data and possibly point me to some examples?
Thanks in advance, Paul
Sample data with desired output will help.
Saravanan
June 22, 2018 at 2:20 pm
Here is a small sample of data for record 12055.
12055 **VOIP Routing call IVR = IVR5001 00:00:17.205 **VOIP Routing call QUEUE = 7500 00:00:17.223 **VOIP Routing call IVR = IVR5003
I can pull IVR5001 with the previous CASE statement, but since the data is variable within the record, I can't seem to get to subsequent values i.e. IVR5003.
Results that I'd like to see are:
RcdID IVRQueue1 IVRQueue2 IVRQueue3 etc
12055 IVR5001 IVR5003
I can then build a report showing what "options" were pressed on the phone.
Thanks,
June 22, 2018 at 11:28 pm
SonicG - Friday, June 22, 2018 2:20 PMHere is a small sample of data for record 12055.
12055 **VOIP Routing call IVR = IVR5001 00:00:17.205 **VOIP Routing call QUEUE = 7500 00:00:17.223 **VOIP Routing call IVR = IVR5003I can pull IVR5001 with the previous CASE statement, but since the data is variable within the record, I can't seem to get to subsequent values i.e. IVR5003.
Results that I'd like to see are:
RcdID IVRQueue1 IVRQueue2 IVRQueue3 etc
12055 IVR5001 IVR5003I can then build a report showing what "options" were pressed on the phone.
Thanks,
Firstly, go and read the following link. At the end of the article, there is a download of The New Splitter Functions.zip. Extract and install the DelimitedSplitN4K function.
NOTE: Since you are using NVARCHAR, the max length that this can handle is 4000.
Tally OH! An Improved SQL 8K “CSV Splitter†Function
Now, this should get you on the right track.DECLARE @CallData NVARCHAR(MAX) = N'12055 **VOIP Routing call IVR = IVR5001 00:00:17.205 **VOIP Routing call QUEUE = 7500 00:00:17.223 **VOIP Routing call IVR = IVR5003';
WITH cteBaseData AS (
SELECT
FieldID = ROW_NUMBER() OVER(ORDER BY splt.ItemNumber)
, FieldValue = CASE WHEN CHARINDEX(N' ', LTRIM(ivr.Item)) = 0 THEN LTRIM(ivr.Item) ELSE LEFT(LTRIM(ivr.Item), CHARINDEX(N' ', LTRIM(ivr.Item)) -1) END
FROM dbo.DelimitedSplitN4K(CAST(REPLACE(@CallData, N'**', N'*') AS NVARCHAR(4000)), N'*') AS splt
OUTER APPLY dbo.DelimitedSplitN4K(splt.Item, N'=') AS ivr
WHERE splt.ItemNumber = 1
OR LTRIM(ivr.Item) LIKE 'IVR%'
)
SELECT
RcdID = MAX(CASE WHEN bd.FieldID = 1 THEN FieldValue END)
, IVRQueue1 = MAX(CASE WHEN bd.FieldID = 2 THEN FieldValue END)
, IVRQueue2 = MAX(CASE WHEN bd.FieldID = 3 THEN FieldValue END)
, IVRQueue3 = MAX(CASE WHEN bd.FieldID = 4 THEN FieldValue END)
, IVRQueue4 = MAX(CASE WHEN bd.FieldID = 5 THEN FieldValue END)
, IVRQueue5 = MAX(CASE WHEN bd.FieldID = 6 THEN FieldValue END)
, IVRQueue6 = MAX(CASE WHEN bd.FieldID = 7 THEN FieldValue END)
FROM cteBaseData AS bd
June 25, 2018 at 10:43 am
Thanks for the information. I'm starting to read through it as well as the recommended "Numbers" or "Tally" Table article. There is a lot to digest and play with.
I appreciate your help on this.
Paul
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply