substr function in universe designer

[Former Member]

Hello All,

I want to create an object in universe which should return a substring.

Example:

object name: class\test

value1: cust_no_123_name_tom

value2: cust_no_1234_name_tom

Now I want to extract tom from the above strings. I cannot use simple substr fuction as the cust_no are different.

it may we 123 or 12345 or 123456

So I need to create a condition that if chars in string = 'name' then display next 3characters.

i tried with below formula but it is not working as expected.

CASE WHEN @Select(class\object) LIKE '%name_%'

THEN substr(@Select(class\object),1,3)
ELSE '0'

END

the output of the above formula is:

value1: cust_no_123_name_tom

value2: cust_no_1234_name_tom

Please suggest.

Thanks & Regards,

Kaustubh Ghate

[Former Member · Accepted solution]

What is your underlying database? This will determine what functions you can use to get what you want.

[Former Member · Accepted solution]

Hi Mark,

Our database is Oracle 11g. That will not be an issue if the name is Dave. our requirment is we just need next 3characters from Name_.

Regards,

Kaustubh

[Former Member · Accepted solution]

Right will not work.

INSTR is a key function for you.

If you only have one instance of Name_ then you'd need something like:

SUBSTR(TABLE.COLUMN,INSTR(TABLE.COLUMN,'name_',1,1)+5,3)

The +5 is to start the substring 5 character after name_, i.e. at the start of the actual name.

Regards,

Mark

[Former Member · Accepted solution]

Hi Mark.

Thank you for your help. The formula worked with aur actual objects.

Can u please help me with one more query. I need everything to be displayed before '_name_tom'. i am trying but not able to implement it correctly.

example:

value: cust_no_1234_name_tom

result:  cust_no_1234

Thanks & Regards,

Kaustubh

[Former Member · Accepted solution]

I'd imagine something like SUBSTR(TABLE.COLUMN,1,INSTR(TABLE.COLUMN,'_name_',1,1)-1) would work. You may need to adjust the -1 to 0 or -2 but it should be okay.

[amitrathi239]

Hi,

Use the Right function to  extract tom from the string.

Create one object like this.

right(@Select(class\object),3)

This object will display the right most three character always.

Amit

[Former Member]

What happens if the name is Dave and not Tom?

[Former Member]

Right function is not working. it is giving Invalid Identifier error.

Kaustubh