fn locate function

[nimmy99]

Hi,

I am trying to use fn locate function under number functions. It's not giving any error. But I am getting 0 output. The function is

{fn locate(DETAILS.USER_ID,'\')} in the SELECT clause.

My object type is Number. I am having data in my table like this

marketing\123
marketing\890
devl\345
dev\563

I would like to find the position of the character '\'.
so that I can get 123,890,345,563 as output in my report

Is it possible. can any one tell?

Thanks

 

[promero]

You can make that operation at report level with the following formula:
[tt]
=Substring(<User Id>, Pos(<User Id>, &quot;\&quot + 1, 100)
[/tt]

 

[nimmy99]

ok. What if the data like this
devl123
dev234
devl\564

The output should be

123
234
564

 

[skrandel]

You're going to need a stored function at the database level to go through the logic of cleansing your data. Your Object in the universe would simply look like:
yourfunc(table.column)

The fact is that your data is in bad shape if you have the situation you just described. You can't expect the reporting tool to clean it for you. Steve Krandel
BASE Consulting Group

http://www.baseconsulting.com

 

[blom0344]

Steve is right about the fact that datacleaning is something to be done at database level or in the ETL process (such as Powermart, letting you start at the back of the string, making a select on the last 3 characters of a string possible , regardless of its length). I think you could do more than going for a stored procedure, such as using a CASE structure in a database view or using it in free-hand SQL. If you would combine a validation with a LENGTH function you could tune the SUBSTR for each different string length. Possibly works at the report level as well:

= If (Length(<Iref01>) = 5) Then SubStr(<Iref01> ,3 ,3) Else <Iref01> .....................

It is not the proper way though, gets a bit long for a great range of stringlengths.... T. Blom
Information analyst
tbl@shimano-eu.com

 

[nimmy99]

Thanks. working fine.