Splitting data into different columns that is shown as a string in 1 Call Variable field

[sarbarnes]

I wonder if anyone can help,

I have been asked to provide a report that takes multiple pieces of data that is being pushed into the Call variable 5 as a string, and then be able to report on each of the separate pieces of information within a report.

My question, what is the best way to present this information in CUIC as separate columns to enable manipulation of the report.

I am not sure if this is a simple process, difficult or can't be done.

Thanks Sarah

[jacparke · Accepted solution]

You can use substring() in the SQL to pull different parts of the string as distinct columns. 

This is a nice article that walks through some of your options:

http://social.technet.microsoft.com/wiki/contents/articles/17948.t-sql-right-left-substring-and-charindex-functions.aspx

Regards,

Jack Parker

[sarbarnes]

Thanks Jack, I have taken a brief look at this and it echos what has been written by Jameson, now I need to try it and see if I can get it to work.

[Jameson Gagnepain]

Sarah,

This is certainly possible within SQL queries. Just keep in mind that any report like this will likely be slow to run.

In SQL, take a look at the SUBSTRING and CHARINDEX functions. Depending on how your data is structured, you will need one or both of these to split the data into multiple CUIC fields.

For example, if you had CallVariable5 filled with the data "123456" with each two characters being a separate data point, you could get the fields you need like this:

SELECT

SUBSTRING(CallVariable5,1,2) AS Field1,

SUBSTRING(CallVariable5,3,2) AS Field2

SUBSTRING(CallVariable5,5,2) AS Field3

If the data lengths are variable, you will need to have some sort of delimiting character in the data. So if you had "12|34|567" as your CallVariable5, you could split it up on the | character like this:

SELECT

SUBSTRING(CallVariable5,1,CHARINDEX('|',CallVariable5)-1) AS Field1,

SUBSTRING(CallVariable5,CHARINDEX('|',CallVariable5)+1,CHARINDEX('|',CallVariable5,CHARINDEX('|',CallVariable5)+1)-1) AS Field2,

SUBSTRING(CallVariable5,CHARINDEX('|',CallVariable5,CHARINDEX('|',CallVariable5)+1)+1,LEN(CallVariable5)) AS Field3

The above gets a bit more complicated as you try and pack more variable-length data into CallVariable 5. Things are much simpler (and faster to execute reports) if each piece of data is a pre-defined length, as you can then use the first method.

-Jameson

[sarbarnes]

Thanks as always Jameson, that gives me something to think about and play around with