Find sub string and return next 5 characters 2

[robcunliffe]

Hello,

cr10, mssql ole db.

i have a field in a db that contain very long text. What i would like to do is find a sub string (which will always be the same) within the field and return the next 5 characters after this sub string.

Can anyone help?

Many thanks

Rob cunliffe

 

[lbass]

Please provide a sample of the entire field, identify the substring you are referring to, and show the result you would like to see.

-LB

 

[robcunliffe]

Name: xxxxxxxxx

Date: xxxxxxxxxxxx

Location: xxx

Room: xxxxx

NAture of call: XXXXXXXXXXXXXXXXXXXX


I would like to return the 5 x's after "room:".

Please note the number of any of the x's can change and go on to 1 or more lines.

thanks

ROob

 

[alexlc]

Hi,

I spent alot of time last year trying to do something similar. This one works if you want to return the data after the category name to the end of the line.

extractstring ({Table.FieldName},"Room: ",chr(13))
//This looks for the text between “Room: “ & the end of the line using the ASCII for carriage return.

This one probably works better for what you want if the data goes over more than one line.

mid({Table.FieldName},instr({Table.FieldName},"Room: ")+5)//this looks for the point in the string where the phrase “Room: ” first appears, +5 is the number of characters in that phrase that you need to crop to return the required text.

Remember to keep the text in the speech marks as it appears on the field, so use spaces if necessary.

Good Luck
Alex C

 

[lbass]

If you use the mid() function, I think it should be:

mid({table.field},instr({table.field},"Room: ")+6, 5)

The second clause specifies the start of the string you want, and the third the number of characters counting from the start.

-LB

 

[robcunliffe]

Hi

I have come back to this thread as i have a further requirement that i cant get my head around.

What changes would i make to the formula below so that it would only return the "x's" after Room Number and Nature of Call?

mid({table.field},instr({table.field},"Room: ")+6, 5)

The number of x's is not fixed and can go on to more than 1 or 2 or 3 lines

Many thanks

 

[dgillz]

Get rid of the ,5 and you will get everything after 'Room: '. Is this what you want?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[robcunliffe]

no i want it to stop when it gets to "nature of call:"

So it only returns the x's after "Room:" and before "Nature of call:

 

[GJParker]

Try

mid({table.field},instr({table.field},"Room: ")+6, (instr({table.field}."Nature of call: " - instr({table.field},"Room: "))

HTH

Gary Parker
MIS Data Analyst
Manchester, England

 

[dgillz]

Aren't these 2 different fields?

If not, then try:

mid({table.field},instr({table.field},"Room: ")+6, instr({table.field},"Nature of call:")-6)


Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[robcunliffe]

many thanks - worked a treat!