string function

shinny · #1 (**permalink**) 12-19-05, 07:03

This is a very newbie question. I am editing a business obects report using WebIntelligence.

I need to limit the string that is returned, I was hoping to do this by allowing everything before the first fullstop. Any suggestions??

Any suggestions would be appreciated.

Thank You

gardenman · #2 (**permalink**) 12-19-05, 07:10

Quote:

Originally Posted by shinny

This is a very newbie question. I am editing a business obects report using WebIntelligence.

I need to limit the string that is returned, I was hoping to do this by allowing everything before the first fullstop. Any suggestions??

Any suggestions would be appreciated.

Thank You

there is function SUBSTR:

(@) values substr('1234567890',1,5)

1
-----
12345

1 record(s) selected.

shinny · #3 (**permalink**) 12-19-05, 08:19

This was my effort

=Substr([CSR Reporting Category Description],.)

it returned unspecified error.

Im sure ive a mistake in there somewhere though
??

Wim · #4 (**permalink**) 12-19-05, 09:32

Quote:

=Substr([CSR Reporting Category Description],.)

I think you're writing Access SQL or something similar.

Your query should look something like this (to get only the first 100 characters):

Code:

SELECT substr(tableorigin.long_field, 1, 100)
FROM tableorigin;

shinny · #5 (**permalink**) 12-19-05, 09:40

it is not possible for me to say how many characters i need. It will never be the same. I only require the first sentence so therefor was trying the find the first fullstop.

Any suggestions?

murali_sb · #6 (**permalink**) 12-19-05, 09:51

Alternatively you can use locate function (substr is provided, no idea why instr is not??), this would be useful if you don't know how many characters to remove using substr.

basically select substr([CSR Reporting Category Description], 1, locate('.', [CSR Reporting Category Description]) -1)

pls note 2 things - depending upon how the string is you might need to use appropriate start point, i mean, 1 should be replaced by appropriate number. Also end point should be string - 1 since locate will give you the location of string so to ignore it in the resultant string you might have to use - 1.

hope this helps.

Wim · #7 (**permalink**) 12-19-05, 10:00

You should look for the first occurenvce of the fullstop character '.', using
POSSTR('source-string','search-string') and using the result of that to limit the length of the field.

Code:

SELECT substr(tableorigin.long_field, 1, POSSTR(tableorigin.long_field, '.'))
FROM tableorigin;

Wim · #8 (**permalink**) 12-19-05, 11:25

Code:

substr([CSR Reporting Category Description], 1, 
    locate('.', [CSR Reporting Category Description]) -1)

What is this "[ .... ]" thing ? Is this DB2 SQL or Business Objects talk ?

shinny · #9 (**permalink**) 12-20-05, 03:12

I dont know what the [..] is all about dont know much about BO but im writing this formula in the formula definition bar in WebIntelligence. it automatically appears when you select an object.

=substr([CSR Reporting Category Description];1;Match([CSR Reporting Category Description];"."))

this did not work, changed locate to match as locate was not an option.
Ive tried changing ; to ' and " to ' but still always returns syntax error

?

shinny · #10 (**permalink**) 12-20-05, 03:15

POSSTR cannot be used either : (

shinny · #11 (**permalink**) 12-20-05, 03:23

=Substr([CSR Reporting Category Description];0;Match([CSR Reporting Category Description];"."))

returns

The expression/sub-expression at position 53 uses an invalid data type. Specify a valid data type. (Error: WIS 10037)

??