Substring Report Function 1

[DrSmyth]

Hi, I've got a big text field in my report and i want to track down certain key words and then report on the text that preceeds them, I've stried the Substring function, but it only seems suitable for looking for single characters.

I've also tried using the locate function combined with the substring function, but again it only really seems to be affective with single characters..

Does anybody have any other ideas??

 

[kprit]

Do you want to cut characters from a specific position in a string or you want particular text to search in the string?

 

[DrSmyth]

I want to find the string, which is a group of words amongst some memo text, and then return the characters that appear in the consecutive 10 spaces after the located text. EG:

I'm looking for string "SPN " and i want the 10 characters after it...

"Blah Blah etc etc SPN this is a test message"

This would return "this is a "

 

[skrandel]

substring(<yourvar>, pos(<yourvar>,&quot;SPN &quot+5,10)

The reason for the +5 is so that the string starts after the &quot;SPN &quot;.

Steve Krandel
Westbay Solutions

http://www.westbaysolutions.com

 

[DrSmyth]

Hi Steve, I've tried this formula in a Report Level User Object, but it's highlighting the &quot;pos&quot; as a bad character.

Am i going wrong somewhere?

 

[Sridharan]

Smyth,

Are you trying to create it as a UDO ??? While defining User Objects you are limited to DB functions and cannot use Reporter Level Function like pos. So create a local variable in the report with Steve's formula. Maybe you can change +5 to +4.

Sri

 

[DrSmyth]

Cool, I've just built the object at report level and it works fine.... Thanks guys.

The only problem i now have is if the string doesn't exist in a record, instead of returning a blank, BO is treating the start of the field as a starting position. This means that i have lots of results that say &quot;Ype &quot; or &quot;inis&quot;. Any ideas how i can get rid of those?

 

[Sridharan]

Smyth,

Jus have to change the formula to use an IF THEN ELSE..

=If Pos(<VALUE> ,&quot;SPN &quot;)+4 = 4 Then &quot;Sorry Not Found&quot; Else SubStr(<VALUE> ,Pos(<VALUE> ,&quot;SPN &quot;)+4 ,10)

The reason for equating with 4 is since we are adding 4 and if the string is not found then POS will return 0.

Also forgot to add in my previous thread. You can even do this at the DB Level but cannot use POS but INSTR for Oracle and LOCATE for SQL Server.

Sri

 

[DrSmyth]

Cheers..... And a very Merry Chrimbo!!!

 

[skrandel]

My mistake, substring should have been SubStr.

Steve Krandel
Westbay Solutions

http://www.westbaysolutions.com