String manipulation over 2 tables in database to application












0












$begingroup$


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) >= '0' AND SUBSTR(input_string, 6, 1) <= '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 say I do not want to create 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] >= '0' && input[5] <= '9'))
return "000" + input.Substring(0,5);
else
return "00" + input;
}


Then the SQL query is to be splitted into 2 database calls



SELECT     *
FROM table1


and



SELECT     *
FROM table2


Those will be returned to its own datatable, and then do a loop or linq to join these 2 tables. But I wonder whether is there any more efficient / better way to do this?









share







New contributor




rcs is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.







$endgroup$

















    0












    $begingroup$


    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) >= '0' AND SUBSTR(input_string, 6, 1) <= '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 say I do not want to create 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] >= '0' && input[5] <= '9'))
    return "000" + input.Substring(0,5);
    else
    return "00" + input;
    }


    Then the SQL query is to be splitted into 2 database calls



    SELECT     *
    FROM table1


    and



    SELECT     *
    FROM table2


    Those will be returned to its own datatable, and then do a loop or linq to join these 2 tables. But I wonder whether is there any more efficient / better way to do this?









    share







    New contributor




    rcs is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
    Check out our Code of Conduct.







    $endgroup$















      0












      0








      0





      $begingroup$


      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) >= '0' AND SUBSTR(input_string, 6, 1) <= '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 say I do not want to create 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] >= '0' && input[5] <= '9'))
      return "000" + input.Substring(0,5);
      else
      return "00" + input;
      }


      Then the SQL query is to be splitted into 2 database calls



      SELECT     *
      FROM table1


      and



      SELECT     *
      FROM table2


      Those will be returned to its own datatable, and then do a loop or linq to join these 2 tables. But I wonder whether is there any more efficient / better way to do this?









      share







      New contributor




      rcs is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.







      $endgroup$




      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) >= '0' AND SUBSTR(input_string, 6, 1) <= '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 say I do not want to create 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] >= '0' && input[5] <= '9'))
      return "000" + input.Substring(0,5);
      else
      return "00" + input;
      }


      Then the SQL query is to be splitted into 2 database calls



      SELECT     *
      FROM table1


      and



      SELECT     *
      FROM table2


      Those will be returned to its own datatable, and then do a loop or linq to join these 2 tables. But I wonder whether is there any more efficient / better way to do this?







      c# oracle





      share







      New contributor




      rcs is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.










      share







      New contributor




      rcs is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.








      share



      share






      New contributor




      rcs is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.









      asked 8 mins ago









      rcsrcs

      1011




      1011




      New contributor




      rcs is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.





      New contributor





      rcs is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.






      rcs is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.






















          0






          active

          oldest

          votes











          Your Answer





          StackExchange.ifUsing("editor", function () {
          return StackExchange.using("mathjaxEditing", function () {
          StackExchange.MarkdownEditor.creationCallbacks.add(function (editor, postfix) {
          StackExchange.mathjaxEditing.prepareWmdForMathJax(editor, postfix, [["\$", "\$"]]);
          });
          });
          }, "mathjax-editing");

          StackExchange.ifUsing("editor", function () {
          StackExchange.using("externalEditor", function () {
          StackExchange.using("snippets", function () {
          StackExchange.snippets.init();
          });
          });
          }, "code-snippets");

          StackExchange.ready(function() {
          var channelOptions = {
          tags: "".split(" "),
          id: "196"
          };
          initTagRenderer("".split(" "), "".split(" "), channelOptions);

          StackExchange.using("externalEditor", function() {
          // Have to fire editor after snippets, if snippets enabled
          if (StackExchange.settings.snippets.snippetsEnabled) {
          StackExchange.using("snippets", function() {
          createEditor();
          });
          }
          else {
          createEditor();
          }
          });

          function createEditor() {
          StackExchange.prepareEditor({
          heartbeatType: 'answer',
          autoActivateHeartbeat: false,
          convertImagesToLinks: false,
          noModals: true,
          showLowRepImageUploadWarning: true,
          reputationToPostImages: null,
          bindNavPrevention: true,
          postfix: "",
          imageUploader: {
          brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
          contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
          allowUrls: true
          },
          onDemand: true,
          discardSelector: ".discard-answer"
          ,immediatelyShowMarkdownHelp:true
          });


          }
          });






          rcs is a new contributor. Be nice, and check out our Code of Conduct.










          draft saved

          draft discarded


















          StackExchange.ready(
          function () {
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fcodereview.stackexchange.com%2fquestions%2f211519%2fstring-manipulation-over-2-tables-in-database-to-application%23new-answer', 'question_page');
          }
          );

          Post as a guest















          Required, but never shown

























          0






          active

          oldest

          votes








          0






          active

          oldest

          votes









          active

          oldest

          votes






          active

          oldest

          votes








          rcs is a new contributor. Be nice, and check out our Code of Conduct.










          draft saved

          draft discarded


















          rcs is a new contributor. Be nice, and check out our Code of Conduct.













          rcs is a new contributor. Be nice, and check out our Code of Conduct.












          rcs is a new contributor. Be nice, and check out our Code of Conduct.
















          Thanks for contributing an answer to Code Review Stack Exchange!


          • Please be sure to answer the question. Provide details and share your research!

          But avoid



          • Asking for help, clarification, or responding to other answers.

          • Making statements based on opinion; back them up with references or personal experience.


          Use MathJax to format equations. MathJax reference.


          To learn more, see our tips on writing great answers.




          draft saved


          draft discarded














          StackExchange.ready(
          function () {
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fcodereview.stackexchange.com%2fquestions%2f211519%2fstring-manipulation-over-2-tables-in-database-to-application%23new-answer', 'question_page');
          }
          );

          Post as a guest















          Required, but never shown





















































          Required, but never shown














          Required, but never shown












          Required, but never shown







          Required, but never shown

































          Required, but never shown














          Required, but never shown












          Required, but never shown







          Required, but never shown







          Popular posts from this blog

          404 Error Contact Form 7 ajax form submitting

          How to know if a Active Directory user can login interactively

          TypeError: fit_transform() missing 1 required positional argument: 'X'