Trim function or truncate variable length of string

[ry1633]

I have a report field which is basically a string that is laid out like "some text; more text" with a semi-colon in the middle.    I need to truncate or remove any text to the right of the semi-colon, which could be of variable length.  Is this possible in Report Studio with the Trim function?

[New_Guy]

Hi,
Try this
substring([Data Item1], 1,(position(';',[Data Item1])-1)). Take off the '-1', if you want the semi colon.
Good luck
New guy

[ry1633]

boom, that worked great.  thanks for the help.  One question,  for some reason, the resulting data in the column is right-justified.  Where every other column is left-justified like standard.  Why is that?

[New_Guy]

Hi,
Glad I could help. Normal behaviour is strings to the left and numbers to the right if I am right.  Please do the alignment manually.
Good luck
New guy

[ry1633]

I've looked all over trying things like -> Style -> Direction and Justification --- haven't found anything to change the alignment on that column.

[ry1633]

Was able to get the column aligned in Workspace Advanced, but not in Report Studio for some reason.

[ry1633]

On related note,  can that substring function be used in Query Studio?   Asking for my users who only have QS.   Unless I can figure out a way to deploy a Report Studio report for use into Query Studio.

[ry1633]

I might need to wrap this in a condition.   Found out from my user that it there is no semicolon in the field name, the entire field needs be shown.   I want to build this into my model in FM as an expression so they can always have it.

[Lynn]

I might need to wrap this in a condition.   Found out from my user that it there is no semicolon in the field name, the entire field needs be shown.   I want to build this into my model in FM as an expression so they can always have it.

position ( string_expression1 , string_expression2 )
Returns the integer value representing the starting position of "string_expression1" in "string_expression2" or 0 when the "string_expression1" is not found.
Example: position ( 'C' , 'ABCDEF' )
Result: 3
Example: position ( 'H' , 'ABCDEF' )
Result: 0

The position function will return zero when the value is not found.

Code Select Expand


case position( ';', [Data Item1] )
when 0 then [Data Item1]
else substring( [Data Item1], 1, ( position( ';', [Data Item1] )-1 ) )
end


It certainly makes sense to include this expression as a query item in the FM model unless you have the option to have this done in the database. It would likely perform better when the parsing is all pre-done in the database, especially if there is any use for this item as a filter.

[ry1633]

great thanks for that - that's a big help. :)

[ry1633]

just for future reference,  my DBA did this in Oracle, and I tried it in Cognos as an alternative.  I didn't think that Cognos supported any of the Oracle REGEXP functions, but apparently this does work also in Cognos.

REGEXP_SUBSTR ([Data Item] , '[^;]*')

Is this any better than using the Case statement; or is it just a different way of accomplishing the same thing?

[jagu07]

hi, i am new to cognos. when i run the excel report from cognos. there are merged cell with text. (this happens bcz of how user is storing the data) Which query can help me bring text in one cell only? Thanks!