SQL things to remember

During my work lately I found out some things about SQL (or is it Oracle SQL) that I didn’t know about and have really made my life easier. Well, maybe not easier, but at least it enriched my knowledge 😉

Subquery on multiple columns

I needed to query a table but the records needed to be available in a subquery. Normally I would write a statement like this:

SELECT a.* 
  FROM a 
 WHERE a.field in (SELECT b.field 
                     FROM b 
                    WHERE ...)

But in this case I needed to check 2 columns for equality. I thought, hey, no problem, I’ll just rewrite the query to use an EXISTS instead of an IN:

SELECT a.* 
  FROM a 
 WHERE EXISTS (SELECT 1 
                 FROM b 
                WHERE b.field1 = a.field1
                  AND b.field2 = a.field2)

That was a nice idea but when I ran this query, it didn’t return any results before I decided to kill the query (after 15 minutes).

There must be a way to query a subset and compare it to multiple columns. After a quick search on the internet I learned that is was possible to use the IN set operator on multiple columns.

SELECT a.* 
  FROM a 
 WHERE (a.field1, a.field2) IN (SELECT b.field1, b.field2
                                  FROM b)

This query returned results within seconds.

CASE as parameter

I have a table in which I need to replace numeric values with their successor. The field is filled like this: xxxTry where it should become xxxTryy incrementing y by 1. Ok, this may look complicated, maybe an example will explain it:

010Tr7 must become 010Tr08

010Tr8 must become 010Tr09

010Tr9 must become 010Tr10.

First I thought to nest a lot of replace statements like this:

SELECT DISTINCT REPLACE( 
                  REPLACE( 
                    REPLACE( 
                      REPLACE( 
                        REPLACE( 
                          REPLACE( 
                            REPLACE( 
                              t.column 
                            ,'Tr9','Tr10') 
                          ,'Tr8','Tr09') 
                        ,'Tr7','Tr08') 
                      ,'Tr6','Tr07') 
                    ,'Tr5','Tr06') 
                  ,'Tr4','Tr05') 
                ,'Tr3','Tr04') 
                ...

This looked ugly (although it does exactly what was needed). So I tried to use the CASE EXPRESSION on the query like this:

REPLACE(t.column 
       ,CASE 
          WHEN t.column LIKE '%Tr9' THEN 'Tr9' 
          WHEN t.column LIKE '%Tr8' THEN 'Tr8' 
          WHEN t.column LIKE '%Tr7' THEN 'Tr7' 
          WHEN t.column LIKE '%Tr6' THEN 'Tr6' 
          WHEN t.column LIKE '%Tr5' THEN 'Tr5' 
          WHEN t.column LIKE '%Tr4' THEN 'Tr4' 
          WHEN t.column LIKE '%Tr3' THEN 'Tr3' 
          WHEN t.column LIKE '%Tr2' THEN 'Tr2' 
          WHEN t.column LIKE '%Tr1' THEN 'Tr1' 
        END 
       ,CASE 
          WHEN t.column LIKE '%Tr9' THEN 'Tr10' 
          WHEN t.column LIKE '%Tr8' THEN 'Tr09' 
          WHEN t.column LIKE '%Tr7' THEN 'Tr08' 
          WHEN t.column LIKE '%Tr6' THEN 'Tr07' 
          WHEN t.column LIKE '%Tr5' THEN 'Tr06' 
          WHEN t.column LIKE '%Tr4' THEN 'Tr05' 
          WHEN t.column LIKE '%Tr3' THEN 'Tr04' 
          WHEN t.column LIKE '%Tr2' THEN 'Tr03' 
          WHEN t.column LIKE '%Tr1' THEN 'Tr02' 
        END)

This is much more verbose and much more work to type it all in, but I think itӳ nice to see that CASE EXPRESSIONS can be used as parameters in other functions. Of course, you wouldnӴ expect differently, but I nice to see it works.

By the way, the shortest query for this issue I came up with is a totally different approach:

substr(t.naam,1,5) || trim(both from to_char(to_number(substr(t.naam,6))+1,'09'))

(Note the trim function needed in the query. Check out Alex Nuijtens blog on why this is).

Just a couple of things I learned lately.

3 thoughts on “SQL things to remember

  1. Another way for the replace:

    SQL> with t as (
    2 select ‘010Tr7’ col from dual union all
    3 select ‘010Tr8’ col from dual union all
    4 select ‘010Tr9’ col from dual union all
    5 select ‘Tr1Tr2′ col from dual
    6 )
    7 select
    8 col,
    9 regexp_replace(t.col,'(Tr)([[:digit:]])’,’\10\2′) col2,
    10 regexp_replace(t.col,'(Tr)([[:digit:]]$)’,’\10\2′) col3
    11 from t;

    COL COL2 COL3
    ———- ———- ———-
    010Tr7 010Tr07 010Tr07
    010Tr8 010Tr08 010Tr08
    010Tr9 010Tr09 010Tr09
    Tr1Tr2 Tr01Tr02 Tr1Tr02

    Col2 shows handling more than one occurrence in the string.
    Col3 demonstrates doing the replace only for the “end of string.”

    Regular expressions are great for this 😉




Leave a Reply to Kim Berg Hansen Cancel reply

Your email address will not be published. Required fields are marked *