String manipulation over 2 tables in database to application I have the following function in the database to do string manipulation:



create or replace FUNCTION convert_string
(
    input_string IN VARCHAR2 
) RETURN VARCHAR2 AS 
output_string VARCHAR(8);
BEGIN
    IF (input_string IS NULL) THEN
        output_string := '';
    ELSIF (NOT (SUBSTR(input_string, 6, 1) &gt;= '0' AND SUBSTR(input_string, 6, 1) &lt;= '9')) THEN
        output_string := '000' || SUBSTR(input_string, 1, 5);
    ELSE
        output_string := '00' || input_string;
    END IF;

    RETURN output_string;
END convert_string;


I'm using the function to perform checking on 2 tables, so my SQL query will be:

SELECT     t1.name
FROM       table1 t1
JOIN       table2 t2 on t1.id = convert_string(t2.id)


But let's say I do not want to create a convert_string function in my database, and would like to do everything on the application side. What is the best way to do it?

The function above can be converted to C# code:



public string ConvertString(input)
{
    if (String.IsNullOrEmpty(input))
        return "";
    else if (!(input[5] &gt;= '0' &amp;&amp; input[5] &lt;= '9'))
        return "000" + input.Substring(0,5);
    else
        return "00" + input;
}


Then the SQL query is to be split into 2 database calls:



SELECT     *
FROM       table1


and

SELECT     *
FROM       table2


Those will be returned to their own data tables, and then do a loop or Linq to join these 2 tables. But I wonder whether there is a more efficient / better way to do this.
Even if I don't know the actual content, joining tables on client-side is basically a bad idea because you have to load the whole content to the client.

As BCdotWEB mentioned in the comment, the prefered way is to consolidate your data model to avoid such hacks. If that is not possible, your solution with the convert_string should work. If you need a more efficient solution, you could use function-based-indexes or virtual columns with indices, which should significantly increase query performance.

One comment to the posted code: The oracle function as well as the c# method do not check the length of the string before accessing the 6'th element which may result in an index out of range exception.