Using substring function in the Universe

[Former Member]

What is the correct syntax of using the substring function in the Universe?

I need to extract the numbers and decimal between the #'s, and the character length varies:

From: DOE, JOHN Added the following entry to work history:

A1S#0.25#

To: 0.25

Please help! Thanks!

[Former Member · Accepted solution]

The following code returned the expected result, which is AWESOME -- THANK YOU!!!!!!!

However, this may interfere with the filter in the same Universe. I was testing the code with and without the filter - CASE WHEN @Select(Scactivity Resolved\Change Desc) LIKE '%A1S%' THEN 'A1' ELSE 'OTHER' END. When I placed the filter on the Webi report it threw an error. I am in the process of re-testing.

CASE WHEN @Select(Scactivity Resolved\Change Desc) LIKE '%A1S%'

THEN substring(@Select(Scactivity Resolved\Change Desc),instr(@Select(Scactivity Resolved\Change Desc,'#'),(length(@Select(Scactivity Resolved\Change Desc)-instr(@Select(Scactivity Resolved\Change Desc,'#'))-1) ELSE '0'

END

[Former Member · Accepted solution]

I refreshed a new Webi report and received the following error msg:

A database error occured. The database error text is: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid length parameter passed to the substring function. (WIS 10901)

The code parsed ok in the Universe, and when I refreshed the data in CR 2008 but it's not working in Webi - and that is the tool I am using to build the report. Please advise. And THANK YOU again for your assistance!

[Former Member · Accepted solution]

What is your BO version with SP details??

Gracias...!!

[Former Member · Accepted solution]

Hi,

I think there was a small syntax error in the formula.A brcket seems to have been in the wrong place:

CASE WHEN @Select(Scactivity Resolved\Change Desc) LIKE '%A1S%'

THEN substring(@Select(Scactivity Resolved\Change Desc),instr(@Select(Scactivity Resolved\Change Desc),'#'),((length(@Select(Scactivity Resolved\Change Desc))-(instr(@Select(Scactivity Resolved\Change Desc),'#')))-1) ELSE '0'

END

Have included a few brackets for clarity too.

Hope this works.

Thanks and Regards,

Smitha.

[former_member142702 · Accepted solution]

The formula will work only if every row returned contains at least two # symbols.

I would recommend to use the following code

CASE WHEN @Select(Scactivity Resolved\Change Desc) LIKE '%A1S#%#%'

THEN substring(@Select(Scactivity Resolved\Change Desc),CHARINDEX('#',@Select(Scactivity Resolved\Change Desc))1,CHARINDEX('#',@Select(Scactivity Resolved\Change Desc),CHARINDEX('#',@Select(Scactivity Resolved\Change Desc))1)-CHARINDEX('#',@Select(Scactivity Resolved\Change Desc))-1)

ELSE '0'

END

Regards,

Stratos

[Former Member · Accepted solution]

It worked - THANKS Stratos!

[Former Member]

Hi,

As I understand your string is of variable length and you need to extract just the number part of the string.

You could try this :

CASE WHEN @Select(Scactivity Resolved\Change Desc) LIKE '%A1S%'

THEN substring(@Select(Scactivity Resolved\Change Desc),instr(@Select(Scactivity Resolved\Change Desc,'#'),(length(@Select(Scactivity Resolved\Change Desc)-instr(@Select(Scactivity Resolved\Change Desc,'#'))-1)

ELSE '0'

END

Assumptions:

The number occurs right after the first #.

The second # is the last character in the string(or rather that the string has just one character after the number).

Thanks and Regards,

Smitha.

[Former Member]

The " is gone however the # on the right is still populating. For example,

0.25#

1#

2.5#

[former_member142702]

Now it should work

CASE WHEN @Select(Scactivity Resolved\Change Desc) LIKE '%A1S%'

THEN substring(@Select(Scactivity Resolved\Change Desc),CHARINDEX('#',@Select(Scactivity Resolved\Change Desc))1,CHARINDEX('#',@Select(Scactivity Resolved\Change Desc),CHARINDEX('#',@Select(Scactivity Resolved\Change Desc))1)-CHARINDEX('#',@Select(Scactivity Resolved\Change Desc))+3)

ELSE '0'

END

Regards,

Stratos

[former_member142702]

Did it work?

Regards,

Stratos

[former_member142702]

Sorry I copied the wrong staetment. THis is the correct one.

CASE WHEN @Select(Scactivity Resolved\Change Desc) LIKE '%A1S%'

THEN substring(@Select(Scactivity Resolved\Change Desc),CHARINDEX('#',@Select(Scactivity Resolved\Change Desc))1,CHARINDEX('#',@Select(Scactivity Resolved\Change Desc),CHARINDEX('#',@Select(Scactivity Resolved\Change Desc))1)-CHARINDEX('#',@Select(Scactivity Resolved\Change Desc))-1)

ELSE '0'

END

Regards,

Stratos

[Former Member]

There are ticket details in the text - I replaced certain letters with x, o, or a. I thought you would need the total number of characters in the text.

0.25#"

DOE, JOHN A Xddxd xxx xxllxwOIg xIxxI xx wxxk xOXxxxI:

A1S#0.25#"O xxxxd Oxck xxxm CJ, xx XxOd xxxx wx xxx XuppxXxd xx Ox gxxxOIg xlxxxX xI xxOX. Xx, xxx quxXxOxI xxmxOIX wxxx xx dx Ixw? XOxxxx wx mxkx xxx dxcOXOxI xx OgIxxx xxOX cxIdOxOxI xId xxvx Ox xxmxvxd xxxm XmxxxX, xx wx xxI xx xxXxxxcx xxp xxlkxxX xId Xxx Ox wx cxI Xxxp Xxmx xx xxx xxxxxOc, xx wx xxdxx x OOggxx cOxcuOx xx xxIdlx xxx OuxXxX wOxxxux xII dxxpX. O'll xxI xx xuI xxOX OI mxIxgxmxIx xId xxx xxXx xx xxx xxxm xx Xxx wxOcx wxI wx wxIx xx gx".

1#"

DXX, JXXN X XddXd xxX xxllxAxng XnxrN xx Axrk xxAxxrN:

A1S#1#"X dxn'x AXX xxxA xn AxnAxrd. X AxA gxxng xx AxXAk xxX xnxXrxxAX xx AXX xx ApxkXA xn xxrxugxpux AXrX xbAXrvXd xA xxA bXXn AuggXAxXd, xxAXvXr, xxA nxx xn AxnAxrd. X AxXAkXd Dxrx xnd dxn'x AXX xnN xndxAxxxxn xxxx xxxA xA xn xnN xlxrI grxupA xxr xxrXAxxld Ixnxxxrxng, Ax X'I AxndXrxng AxXrX xxX xlxrI xA AxIxng xrxI. Xx Axuld bX xxxx xxXrX xA nx xnxXnxxxn xx bX xlxrIxng xn xxxA xx xll xnd xxX xlxrIA Axuld bX AxIxng xrxI xn xld Ixnxxxrxng AxurAX xxxx xA nx lxngXr vxlxd (xX, xxxA AlxppXd xxrxugx xxX ArxAkA AxIXxxA). X AXnx xn XIxxl xx AurxxA JxnXA xx AXX xx xX Axn xXll Axxx xxX AxurAX xx xxX xlxrI xA (xX, Axxx Ixnxxxrxng ANAxXI xAxuxllN gXnXrxxXd xxX xlxrI)."

0.5#

DOE, JOHN A Xaaxa xax OolloxixI xxxIx xo xoIk aisxoIx:

A1S#.05# "XIxxsOxIIixI xo IXS XixI x quxux OoI xaaixioxxl ixOoIoxxiox xxa Ixcoooaxxioxs."

[former_member142702]

Try this code.

CASE WHEN @Select(Scactivity Resolved\Change Desc) LIKE '%A1S%'

THEN substring(@Select(Scactivity Resolved\Change Desc),CHARINDEX('#',@Select(Scactivity Resolved\Change Desc))1,CHARINDEX('#',@Select(Scactivity Resolved\Change Desc),CHARINDEX('#',@Select(Scactivity Resolved\Change Desc))1)-CHARINDEX('#',@Select(Scactivity Resolved\Change Desc))+2)

ELSE '0'

END

Regards,

Stratos

[Former Member]

Can I send you the text via email - I cannot post the details in a public forum. Please let me know. Thanks!

[former_member142702]

Just replace the names with dummies.

Regards,

Stratos

[Former Member]

I tested and it's almost there. It's still including the pound sign (on the right), and in some instances it's also returning a quote. The following are a couple of examples of what it's returning:

0.25#"

0.5#

1#"

[former_member142702]

Can you provide the full text beside the parsed text for the examples you have sent? I want to see the exact entries.

Regards,

Stratos

[Former Member]

Apologies for the delayed response - I was gone for the day when you responded. I am testing now and will come back to you in a bit with the outcome. Thanks again!

[Former Member]

It removed the text on the left however, there is text to the right of the number including the second pound sign.

[former_member142702]

Can you give me an example?

Regards,

Stratos

[former_member142702]

Try this:

CASE WHEN @Select(Scactivity Resolved\Change Desc) LIKE '%A1S%'

THEN substring(@Select(Scactivity Resolved\Change Desc),CHARINDEX('#',@Select(Scactivity Resolved\Change Desc))1,CHARINDEX('#',@Select(Scactivity Resolved\Change Desc),CHARINDEX('#',@Select(Scactivity Resolved\Change Desc))1)-CHARINDEX('#',@Select(Scactivity Resolved\Change Desc))+1)

ELSE '0'

END

[former_member142702]

Did it work?

Regards,

Stratos

[Former Member]

It parsed ok. I saved my universe, exported it to the repository, and am refreshing the report now to confirm results. I will let you know the outcome. THANKS!!!!!!!

[Former Member]

I copied/pasted your code into the object properties in the Select and the parse failed. The following is the error msg:

Parse failed: Exception DBD, [Microsoft][ODBC SQL Server Driver][SQL Server] Statement(s) could not be prepared.. State: 42000

[former_member142702]

Try this one. There were somee missing parenthesis.

CASE WHEN @Select(Scactivity Resolved\Change Desc) LIKE '%A1S%'

THEN substring(@Select(Scactivity Resolved\Change Desc),CHARINDEX('#',@Select(Scactivity Resolved\Change Desc))1,LEN(@Select(Scactivity Resolved\Change Desc))-CHARINDEX('#',@Select(Scactivity Resolved\Change Desc))1)

ELSE '0'

END

Regards,

Stratos

[Former Member]

It returned results other than just 0's. However it's not what I need. I need to extract the numbers between the # signs.

Current text within the field:

DOE, JOHN Added the following entry to work history:A1S#0.25#

Sample of expected result: 0.25

[former_member142702]

Try the following

CASE WHEN @Select(Scactivity Resolved\Change Desc) LIKE '%A1S%'

THEN substring(@Select(Scactivity Resolved\Change Desc),CHARINDEX('#',@Select(Scactivity Resolved\Change Desc)1,LEN(@Select(Scactivity Resolved\Change Desc)-CHARINDEX('#',@Select(Scactivity Resolved\Change Desc)1)

ELSE '0'

END

Regards,

Stratos

[Former Member]

The leading wildcard was not there - not sure how it was deleted in the process of updates I made. I have both wildcards now and saved/exported my universe. I am running the report now - I will reply back with the results in a bit. Thanks!

[Former Member]

I did try extending the length to 30 and still returns all 0's.

[former_member142702]

What is failing is the when clause.

Can you please try to issue an SQL statement with the LIKE condition you are using in the WHERE clause directly in the database and see if it returns any results?

Regards,

Stratos

[Former Member]

Yes - they are all 0's.

[Former Member]

My apologies - clicked the post message button to soon. Please advise. Thanks!

[former_member142702]

Try this one

CASE WHEN @Select(Scactivity Resolved\Change Desc) LIKE '%A1S%'

THEN substring(@Select(Scactivity Resolved\Change Desc),5,30)

ELSE '0'

END

DO you get any results?

Regards,

Stratos

[Former Member]

Change the code to It's still not finding a "true" result - values are all 0's.

CASE WHEN @Select(Scactivity Resolved\Change Desc) LIKE 'A1S#%'

THEN substring(@Select(Scactivity Resolved\Change Desc),5,30)

ELSE '0'

END

[Former Member]

One other item to note is that the field which contains the string below does not always include the A1S#0.25#. Which means I need to include a case statement. I created an Object in my Universe and attempted to write the statement and it did not return the expected result.

CASE WHEN @Select(Scactivity Resolved\Change Desc) LIKE 'A1S#%'

THEN substring(@Select(Scactivity Resolved\Change Desc),3,0)

ELSE '0'

END

I need to extract the numbers and decimal between the #'s, and the character length varies:

From: DOE, JOHN Added the following entry to work history:

A1S#0.25#

To: 0.25

[former_member142702]

The syntax is SUBSTRING ( expression ,start ,length )

So your object should look like this:

CASE WHEN @Select(Scactivity Resolved\Change Desc) LIKE 'A1S#%'

THEN substring(@Select(Scactivity Resolved\Change Desc),5,20)

ELSE '0'

END

As you can see I set a quite long length (20) to get over the problem with the varying length.

Regards,

Stratos

[Former Member]

It's a Microsoft SQL Server Database.

[Former Member]

It's a SQL db.

[former_member142702]

Which vendor (eg. oracle, microsoft, ibm)? YOu can use directly the substring function in the SQL dialect that your vendor supports in your universe.

Regards,

Stratos

[former_member142702]

What kind of data source do you access with your universe?

Regards,

Stratos