Results 1 to 13 of 13

Thread: string function

  1. #1
    Join Date
    Mar 2005
    Location
    Ireland
    Posts
    35

    Unanswered: string function

    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

  2. #2
    Join Date
    Apr 2004
    Posts
    54
    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.

  3. #3
    Join Date
    Mar 2005
    Location
    Ireland
    Posts
    35
    This was my effort

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

    it returned unspecified error.

    Im sure ive a mistake in there somewhere though
    ??

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    =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;
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Mar 2005
    Location
    Ireland
    Posts
    35
    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?

  6. #6
    Join Date
    Dec 2005
    Posts
    39
    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.

  7. #7
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    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;
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  8. #8
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    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 ?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  9. #9
    Join Date
    Mar 2005
    Location
    Ireland
    Posts
    35
    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

    ?

  10. #10
    Join Date
    Mar 2005
    Location
    Ireland
    Posts
    35
    POSSTR cannot be used either : (

  11. #11
    Join Date
    Mar 2005
    Location
    Ireland
    Posts
    35
    =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)

    ??

  12. #12
    Join Date
    Dec 2005
    Posts
    39
    is this really DB2 query then? maybe you need to consult Web Intelligence/BO documentation.

  13. #13
    Join Date
    Mar 2005
    Location
    Ireland
    Posts
    35
    Its a DB2 database that is why i coose this forum

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •