How to separate number from the text and numeric togheher data.

[cognos74]

Hi, I have a question,

in my report one of the column has values like below,  I need to get only the numbers from it means 15,45 etc..
Individual;15
Individual;45
Group:45
Group;30
etc..
any idea how to get that. I think using position function it might possible but no idea how to use that function I never used.

[sdf]

have you tried SUBSTR function?
i can see you different number of characters(before the numbers), so you might need to use CHAR_LENGTH as well.

[Lynn]

The best place to do these types of string manipulations is in the database, not in the report. I realize that isn't always an option but it never stops me from saying it anyway.

So if you must do it in the report, can you tell us what database you use? Presumably this is a relational data source?

The DB2 "translate" function is illustrated below. The first argument is the thing to be translated so you'd put a reference to your query item in there, not the string example as I've done. The second argument is the thing to replace the unwanted characters with, which is a blank in the below example. The third argument contains all the unwanted characters. I've used all the upper case and lower case letters plus a semi-colon based on the example you provided. If you have other special characters like % or * or whatever you'd need to include those as well.

Code Select Expand


trim ( translate('Individual;15',' ', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ;' ) )


The result of the translate function would be '           15' and wrapping it in a trim function gets rid of any leading or trailing blanks leaving you with '15'. You could wrap a cast function around that to leave you with a number 15 if that's where you're going.

I believe oracle has a translate function although the syntax might be different. I think SQL Server has a replace function.

[bdbits]

Totally agree with Lynn this should be done in ETL, but...

I think this will do it.
substring('Individual;15',position(';','Individual;15'))

If those examples are actually a data item named [SomeItem], then this.
substring([SomeItem],position(';',[SomeItem]))