split ssrs expression between 2 words

Question

I have a requirement to split a result up into 3 separate expressions, Using sql server 2008.

The result i currently get is for example. "person: man tel: yes rel: christian msg: misc text"

I need it split into:

"Person: man" "Tel: yes" "Rel: Christian" "Msg: misc text"

I have been using an iif statement to return the bit before "tel" but cant get it to return only between tel and rel etc. The part before the colon will remain static whichever result is returned in my dataset, but the part after the colon could be anything (of any length too) before getting to the next section. Can anyone help please? The other similar questions on the forum have been helpful with the first expression, but the later ones are a little more difficult.

Is there always only 3 parts in the string? This is going to be easier to do in SQL than in SSRS. Even better would be to modify the schema so it actually stores these bits separately.... but that usually isn't an option unless you have control over the application too. — Mike D., Jul 09 '15 at 01:52
To do it in SSRS you should try with custom code, create a function that splits it and return your results — Sébastien Sevrin, Jul 09 '15 at 06:47
@Mike D - I can modify the table that the data is coming from, or split the column into multiple columns if required in SQL. There will always be Person: xxxx Tel: xxx Rel: xxxx and Msg: xxxx data coming in in that order from another datasource, so at least we have that to work from! What did you have in mind? Thanks. — user3735855, Jul 09 '15 at 09:55
@Sebastien Sevrin , what would you suggest for the custom code please? — user3735855, Jul 09 '15 at 09:56

score 0 · Answer 1 · answered Jul 09 '15 at 14:47

This can be very easily achieved in SQL rather than SSRS. I would highly recommend that this be done in SQL. You can use CHARINDEX function to split the string into 4 separate columns (person, tel, Rel, Message)

score 0 · Answer 2 · answered Jul 10 '15 at 00:02

This can be achieved in SSRS pretty much the same way that you would with SQL - using INSTR (CHARINDEX in SQL Server) and MID (SUBSTRING in SQL) to find the locations and parse the string. There is an issue where you're using a space as a delimiter and your data has spaces in it ("Msg: misc text").

First I would create a calculated column in the dataset to change your space delimiter to another character to make it easier to find the space breaks. I usually use a Pipe ( | ) because it's rarely used.

=REPLACE(REPLACE(REPLACE(FIELDS!YourFIELD!.VALUE, ": ", "`"), " ", "|"), "`", ": ")

Column 1: =MID(First(Fields!txt2.Value, "DISPUTES"), 1, INSTR(Fields!txt2.Value, "|") - 1)

Column2: =MID(MID(Fields!txt2.Value, INSTR(Fields!txt2.Value, "|") + 1, LEN(Fields!txt2.Value) ), 1, INSTR(MID(Fields!txt2.Value, INSTR(Fields!txt2.Value, "|") + 1, LEN(Fields!txt2.Value) ), "|") - 1)

Columns 3 and 4 would be similar - though a little more complicated.

Thankyou, I understand your logic for this. Thanks. – user3735855 Jul 10 '15 at 08:43 — user3735855, Jul 10 '15 at 08:43

Mike D. · Accepted Answer · 2015-07-10T02:07:24.717

0

For a SQL method this works for the test I did:

SELECT LEFT(txt, CHARINDEX('tel:',txt)-2) AS part1,
    SUBSTRING(txt, CHARINDEX('tel:',txt), CHARINDEX('rel:',txt)-CHARINDEX('tel:',txt)) AS part2,
    SUBSTRING(txt, CHARINDEX('rel:',txt), CHARINDEX('msg:',txt)-CHARINDEX('rel:',txt)) AS part3,
    RIGHT(txt, LEN(txt)-CHARINDEX('msg:',txt)+1) AS part4
FROM (
    SELECT 'person: man tel: yes rel: christian msg: misc text' AS txt
) AS t

edited Jul 10 '15 at 02:07

answered Jul 10 '15 at 00:03

Mike D.

4,034
2
26
41

1

D - this is perfect thankyou. I have amended it slightly, but understand it perfectly. Thanks for your time. – user3735855 Jul 10 '15 at 08:42

split ssrs expression between 2 words

3 Answers3