Converting ASCII character code in SQL and Oracle

[bbrooksux]

We are working on porting our OEM solution from SQL to Oracle.  Most of the framework elements are dbms agnostic for the most part, however one section of our framework dealing with our custom properties is utilizing a char function that is SQL specific.  Essentially we're using 3 functions -- substring, position and char -- to segment out a multi value field based on the ASCII character code 2.  The SQL used is as follows:
substring(
    [Custom Properties].[IN_PROP].[DISPLAY_FORMAT],
    0,
    position ( char(2), [Custom Properties].[IN_PROP].[DISPLAY_FORMAT] ) )

We would like to find a way to make this more agnostic, if at all possible, and the char function is what is causing the problem in this item.  Does anyone have suggestions on how best to separate out the data?

Here's a sample of the raw data:
currencytrueUSDfixed2

expected output:
currency

[blom0344]

Define a database function (1 for oracle, 1 for SQL server) That performs the entire action. Use the same name in both RDBMS. Import the function and use this in a Cognos SQL. Perhaps this will allow you to have one single Cognos object to perform the job on both SQL server / Oracle.
(No idea whether this will work    :-\  )

[bbrooksux]

Can you give an example of how to create the database function?  That seems like a great solution, but getting over that first hump seems like the big obstacle right now.   Thanks!

[blom0344]

T-SQL example:

Code Select Expand



CREATE FUNCTION [dbo].[BBROOKSSUX]
(
@tableprimkey varchar(50)
)
RETURNS varchar(200)
AS
BEGIN
DECLARE @locale varchar(200)

set @locale = substring( DISPLAY_FORMAT, 0,position ( char(2), DISPLAY_FORMAT) )
from CUSTOMTABLE where PRIMKEY = @tableprimkey
)
RETURN (@locale)

END



Assuming that the CUSTOMTABLE in the database has a primary key (in my example a string, would probably be an integer) you can reference in the function to get 1 result from the function call.

[blom0344]

Just out of curiousity.. Did you manage to accomplish your goal?