Report Builder 3.0 Expression to extract 2 parts of a string

Question

Report Builder 3.0 Expression to extract 2 parts of a string

mschaper

Ten Centuries

Points: 1120
More actions
March 17, 2015 at 10:17 am

#302083

I'm developing a report in Report Builder 3.0. One of the fields returns one of the following notes:
Audit Status changed from STOP to RELEASED.
Audit Status changed from STOP to RFS Pend.
Audit Status changed from RELEASED to RFS Pend.
Audit Status changed from STOP to ODY Pend.
Audit Status changed from ODY Pend to RELEASED.
There may be other notes but these are the ones that I have seen so far.
What I would like to do is to extract 2 parts of each note with an expression.
The first part is the words between "from" and "to" (STOP, RELEASED, ODY Pend, etc.).
the second part is the words after "to" (RELEASED, RFS Pend, ODY, etc.)
I've tried split and it works but it makes the report run much longer. I've looked at using MID but I'm not sure how to write the expression since the starting point of each part is different.
Any help with the expression to extract this information would be greatly appreciated. Thanks for your help..........

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004410 More actions · Answer 1

mschaper (3/17/2015)
I'm developing a report in Report Builder 3.0. [font="Arial Black"] One of the fields returns one of the following notes[/font]:
Audit Status changed from STOP to RELEASED.
Audit Status changed from STOP to RFS Pend.
Audit Status changed from RELEASED to RFS Pend.
Audit Status changed from STOP to ODY Pend.
Audit Status changed from ODY Pend to RELEASED.
There may be other notes but these are the ones that I have seen so far.
What I would like to do is to extract 2 parts of each note with an expression.
The first part is the words between "from" and "to" (STOP, RELEASED, ODY Pend, etc.).
the second part is the words after "to" (RELEASED, RFS Pend, ODY, etc.)
I've tried split and it works but it makes the report run much longer. I've looked at using MID but I'm not sure how to write the expression since the starting point of each part is different.
Any help with the expression to extract this information would be greatly appreciated. Thanks for your help..........

"One of the fields"... is that "field" in an SQL Table? If so, what's the name of the table?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mschaper Ten Centuries Points: 1120 More actions · Answer 2

The sql is below. The field is "note_txt" and the table name is "notes". I've replace the IN with a parameter and have also created 2 parameters for the date fields.

Thanks for your help.......Mike

select n.note_id, n.usr_creat, n.ecr_user_name, n.note_txt_ln, n.creat_tmsp

from notes n

where n.note_typ = 8

and n.note_stat = 'IA'

and n.usr_creat in ('E247D9', 'E254GR', 'E285N1', 'E641NZ', 'E430KB')

and substr(n.note_txt_ln,1,12)= 'Audit Status'

and n.creat_tmsp between '2015-01-01 00:00:00' and '2015-03-16 23:59:59';

Jeff Moden SSC Guru Points: 1004410 More actions · Answer 3

Apologies for the extreme delay. The following demonstrates one way to do this.

--===== This just creates a table and populates it with sample data all on the fly.

-- It is NOT a part of the solution.

SELECT *

INTO #Notes

FROM (

SELECT 'Audit Status changed from STOP to RELEASED.' UNION ALL

SELECT 'Audit Status changed from STOP to RFS Pend.' UNION ALL

SELECT 'Audit Status changed from RELEASED to RFS Pend.' UNION ALL

SELECT 'Audit Status changed from STOP to ODY Pend.' UNION ALL

SELECT 'Audit Status changed from ODY Pend to RELEASED.'

) d (Note_Txt)

;

--===== This solves the problem for the given data.

WITH

cteDelimit AS

(

SELECT Note_Txt

,Pf = 27

,Pt = CHARINDEX(' to ',Note_Txt)+4

,Pe = LEN(Note_Txt)

FROM #Notes

)

SELECT FromStatus = SUBSTRING(Note_Txt,Pf,Pt-Pf-3)

,ToStatus = SUBSTRING(Note_Txt,Pt,Pe-Pt)

,Note_Txt

FROM cteDelimit

;

As a bit of a sidebar, take a look at the article at the first link under "Helpful Links" in my signature line below. It'll help you get answers much more quickly. I got busy and I thought someone else would have picked up on this by now but the lack of readily consumable data may have been a put-off for many.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mschaper Ten Centuries Points: 1120 More actions · Answer 4

Jeff..............Thanks for the help. No problem on the delay. I'm really new to writing sql and use these forums to help solve the things I'm trying to do. I'm an accountant by trade but we are moving into more of a report writing role. I had a 2 day class in sql; which was pretty basic. Very small tables and only a few simple joins. The tables I'm working with are much larger and there are many more to work with. After trying a number of different things, this is what I came up with. Not sure how good this is but it seems to do what I was looking to do in the SELECT statement

substr(note_txt_ln, 27,(cast(instr(note_txt_ln, 'to') as integer) - 28) ) as Status_FROM,

substr(note_txt_ln, (cast(instr(note_txt_ln, 'to') as integer) +4), (cast(length (note_txt_ln) as integer) - (cast(instr(note_txt_ln, 'to') as integer) +4) )) as Status_TO,

Thanks again for taking the time to reply.......