11 Replies Latest reply: May 7, 2012 3:10 PM by AlbertoFaenza RSS

    SQL function help

    934934
      Hi, I am trying to write a sql query to replace the string starting with '<' with NULL. Right now I am using CASE statement to replace with NULLs. Is there any function to replace with NULLs for all the strings starting with '<%'.

      Example:

      CASE When field_name LIKE '<%' then NULL else field_name

      (before) After
      <NA> NULL
      <UK> NULL

      I want to replace the above sql suery with a single sql function without using CASE statements.

      I tried to use REPLACE function, but it replaces only single character. I want to replace the whole string

      Thanks in advance.
        • 1. Re: SQL function help
          thomaso
          Simple DML sould do it:
            UPDATE <my table name> SET field_name = null WHERE field_name like '<%';
          HTH
          Thomas
          • 2. Re: SQL function help
            John Spencer
            update table_name
            set column_name = null
            where column_name like '<%'
            John
            • 3. Re: SQL function help
              934934
              Hi Thomas,

              Thanks for your reply. But I am trying to select multiple fields from different tables. I am extracting almost 100 fields, for which I need to default all the values to NULL for the field values starting with '<%'.

              The query you sent me works for to extract & update a single field. Even if it works for multiple fields, then I need to define 'Update.... SET....' for all the fields I am pulling out. Please help me if there is any possibility.

              And moreover, to my knowledge, there is NO function to define for a table on the whole. Meaning to say, all the functions are attribute level not table level.
              • 4. Re: SQL function help
                934934
                Hi John,

                Thanks for your reply. But I am trying to select multiple fields from different tables. I am extracting almost 100 fields, for which I need to default all the values to NULL for the field values starting with '<%'.

                The query you sent me works for to extract & update a single field. Even if it works for multiple fields, then I need to define 'Update.... SET....' for all the fields I am pulling out. Please help me if there is any possibility.

                And moreover, to my knowledge, there is NO function to define for a table on the whole. Meaning to say, all the functions are attribute level not table level.
                • 5. Re: SQL function help
                  AlbertoFaenza
                  Hi,

                  it's kind of difficult to understand your requirement.
                  You said: +"I am trying to select multiple fields from different tables"+ and +"there is NO function to define for a table on the whole"+.

                  If I understood well you have a certain number of table with more than 100 columns which you might need to update.

                  However I do not understand if you are joining the tables or if you are performing actions of different tables in different statements.
                  Another information that might be useful (just to have an idea) could be (more or less) the number of records that might be affected by this operation and the number of records in the tables.

                  SQL don't have a function that update all columns in one goal as far as I know.
                  But if you try to describe better what you need maybe someone can help.

                  Regards
                  Al
                  • 6. Re: SQL function help
                    kendenny
                    Something like this?
                    CREATE OR REPLACE FUNCTION nullify_less(i_string VARCHAR2)
                      RETURN VARCHAR2 IS
                    BEGIN
                      IF SUBSTR(i_string,1,1) = '<' THEN
                        RETURN NULL;
                      ELSE
                        RETURN i_string;
                      END IF;
                    END;
                    /
                    • 7. Re: SQL function help
                      John Spencer
                      If you are updating multiple columns in a table, then you have two possible choices. Use multiple update statements like:
                      update table_name
                      set column1 = null
                      where column1 like '<%';
                      
                      update table_name
                      set column2 = null
                      where column2 like '<%';
                      ...
                      update table_name
                      set columnn = null
                      where columnn like '<%';
                      which is extraordinarily inefficient, or do a single update of all columns at one time like:
                      update table_name
                      set column1 = null if <something to determine if it begins with < is true>,
                          column2 = null if <something to determine if it begins with < is true>
                          ...
                          columnn = null if <something to determine if it begins with < is true>
                      To determine if each column begins with < you could use a function as kendenny showed, but that would require a context switch between sql and pl/sql for each column you potentially want to update, which will, unless the table is really small, be a substantial overhead. The most efficient way would be to use the built-in conditional functionality of Oracle like ohh, a CASE statement. If there could be a substantial number of rows with no columns beginning with <, then I would also add a predicate to eliminate those rows from the update. Something like:
                      update table_name
                      set column1 = case when column1 like '>%' then NULL else column1 end,
                          column2 = case when column2 like '>%' then NULL else column2 end,
                          ...
                          columnn = case when columnn like '>%' then NULL else columnn end
                      where column1 like '>%' or
                            column2 like '>%' or
                            ...
                            columnn like '>%'
                      I know you said you did not want to use case, but it is the most efficient way. If you just have some inherent distaste for typing the word case, then decode would work as well. Something like:
                      update table_name
                      set column1 = decode(instr(column1, '>'), 1, NULL, column1),
                          column2 = decode(instr(column2, '>'), 1, NULL, column2),
                          ...
                          columnn = decode(instr(columnn, '>'), 1, NULL, columnn)
                      where column1 like '>%' or
                            column2 like '>%' or
                            ...
                            columnn like '>%'
                      However, case just seems a lot cleaner and more readable to me.

                      John

                      Edited by: John Spencer on May 7, 2012 1:02 PM
                      Fixed major formatting mess
                      • 8. Re: SQL function help
                        934934
                        Hi John, Alberto, Kendenny,

                        Thanks all for taking time in replying. That was really helpful, but it didn't serve my purpose.

                        First of all, I hope everybody understood what my question is. I am trying to pull some fields- say 100 fields (from different tables, using joins/conditions,etc...) . When I pull the fields, I want to default all the field values starting with '<' to NULLS. Currently I am using CASE statement to do that. So, if I pull 100 fields, I need to write those many CASE statements, which make my query lengthy (Though I hope this is the easy and best way).

                        My question is, I just want to know is there any other sql-function in Oracle, instead of writing those lengthy CASE statements which can replace '<' with NULLs. (may be some functions like REPLACE, TRANSLATE - I know these functions cannot serve my purpose.)

                        Example:

                        CASE when first_name LIKE '<%' then NULL else first_name end
                        CASE when 'gender' LIKE '<%' then NULL else 'gender' end
                        etc.....

                        instead of those statements, I am just wondering if there is any function like-

                        sql_function(substr(first_name,1,1), '<', NULL)
                        sql_function(substr(gender, 1,1,), '<', NULL)
                        etc.....

                        I hope everybody i clear now
                        • 9. Re: SQL function help
                          sb92075
                          user13148289 wrote:
                          Hi John, Alberto, Kendenny,

                          Thanks all for taking time in replying. That was really helpful, but it didn't serve my purpose.

                          First of all, I hope everybody understood what my question is. I am trying to pull some fields- say 100 fields (from different tables, using joins/conditions,etc...) . When I pull the fields, I want to default all the field values starting with '<' to NULLS. Currently I am using CASE statement to do that. So, if I pull 100 fields, I need to write those many CASE statements, which make my query lengthy (Though I hope this is the easy and best way).

                          My question is, I just want to know is there any other sql-function in Oracle, instead of writing those lengthy CASE statements which can replace '<' with NULLs. (may be some functions like REPLACE, TRANSLATE - I know these functions cannot serve my purpose.)

                          Example:

                          CASE when first_name LIKE '<%' then NULL else first_name end
                          CASE when 'gender' LIKE '<%' then NULL else 'gender' end
                          etc.....

                          instead of those statements, I am just wondering if there is any function like-
                          nobody here is preventing you from writing your own custom function.
                          • 10. Re: SQL function help
                            AdamMartin
                            You can use regular expressions. For example:
                            select regexp_replace('<NA>','^<.?+','') 
                            from dual
                            This replaces only strings that start with '<' replacing the entire string with null.

                            To help decipher the regular expression pattern in case you are not familiar with them...

                            ^ = matches only at the start of the string
                            < = the character we are looking for
                            . = any character (so anything following '<' will match the pattern and be replaced too)
                            ? = makes the previous character (the period/dot) optional (so we will also replace '<' alone if it is the only character in the string)
                            + = greedily repeat the previous character as many times as possible (find as many characters after '<' as possible and replace them all)
                            • 11. Re: SQL function help
                              AlbertoFaenza
                              Hi,

                              Adam Martin posted a good solution. I hope that it is clear to you that you have to replace the string '<NA>' with your column(s).

                              I.e.:
                              SELECT REGEXP_REPLACE (a.col1, '^<.?+', '') AS col1
                                   , REGEXP_REPLACE (a.col2, '^<.?+', '') AS col2
                                FROM table1 a;
                              {code}
                              
                              
                              You can use also the DECODE and SUBSTR functions to have the same result:
                              The code below will get the first character with SUBSTR function and the DECODE function will return NULL if the value of the first character is "<" otherwise the value of the column.
                              
                              {code:sql}
                              SELECT DECODE(SUBSTR(a.col1,1,1),'<', NULL, a.col1) AS col1
                                   , DECODE(SUBSTR(a.col2,1,1),'<', NULL, a.col2) AS col2
                                FROM table1 a;
                              {code}
                              
                              but using REGEXP_REPLACE is easier to write.