August 24th, 2010 by depesz | Tags: , , , , , , | 10 comments »
Did it help? If yes - maybe you can help me?

On 24th of August, Takahiro Itagaki committed patch:

Log Message:
-----------
Add string functions: concat(), concat_ws(), left(), right(), and reverse().
 
Pavel Stehule, reviewed by me.

What are these functions?

concat() is just functional counterpart to || operator, with the ability to handle many strings at the same time:

$ select concat( 'post', 'gres', 'ql' );
   concat   
------------
 postgresql
(1 row)

Pretty simple, but useful if you're doing a lot of concatenations.

concat_ws() is similar, but it treats its first argument as separator, allowing:

$ select concat_ws( ' ', 'hubert', 'depesz', 'lubaczewski' );
         concat_ws         
---------------------------
 hubert depesz lubaczewski
(1 row)

left() and right() have the same meaning, but are used on different parts of string:

$ select left( 'postgresql', 4 );
 left 
------
 post
(1 row)

Generally it's like substring, but we have one nice feature:

$ select left( 'postgresql', -2 );
   left   
----------
 postgres
(1 row)

That is – if the length parameter is negative it will chop abs(length) characters from “the other side".

Right examples:

$ select right( 'postgresql', 3 );
 right 
-------
 sql
(1 row)
 
$ select right( 'postgresql', -4 );
 right  
--------
 gresql
(1 row)

All of these were relatively simply available previously. But the last function is (in my not so humble opinion) the most important. reverse().

It's functionality is trivial:

$ select reverse( 'postgresql' );
  reverse   
------------
 lqsergtsop
(1 row)

But the fact that we have it now in core PostgreSQL, means we can have fast searches using “LIKE ‘%something'" – technique for this was described earlier, but we were missing fast reverse function. Now it's in core. Great.

  1. 10 comments

  2. # Thom Brown
    Aug 24, 2010

    I think that having these functions will make migration from MySQL that much less painless. 🙂 Although I believe MySQL’s version of LEFT() and RIGHT() don’t support negative lengths.

  3. Is that what these are for? MySQL migration? just wondering on the… purpose behind them…

  4. # mrova
    Aug 24, 2010

    It makes butterflies in our poor programmers life 🙂

  5. # Wen
    Aug 24, 2010

    Can “concat_ws” be used as an aggregate function?

  6. Aug 24, 2010

    @Wen:
    i doubt, but there is no point: http://www.depesz.com/index.php/2010/02/17/waiting-for-9-0-string_agg/

  7. # Wen
    Aug 24, 2010

    Thanks. Missed that post, can’t wait for 9.0 to come out.

  8. # mpawlikowski
    Aug 25, 2010

    small formatting problem between section about concat_ws and left function 😉 Perhaps unclosed

    
    	
  9. Aug 25, 2010

    @mpawlikowski:
    thanks, fixed.

  10. # JSPATZ
    Aug 26, 2010

    What about concatenation with NULL?
    Normally ‘TEXT’ || NULL = NULL, what about the new functions, is that equal?

    If you have many strings it is very unhandy to write coalesce( Field1, ”) || coalesce( Field2, ”) || coalesce( Field1, ”) …and so on.

    In my projects I already made a concat-Function which is equal to the concat above and an concatN Function which does an coalesce( Param, ”) on every parameter.

    Thanks

  11. Aug 26, 2010

    @JSpatz:

    # select concat('a', NULL, 'b');
    concat
    --------
    ab
    (1 row)

    # select concat_ws('-=>', 'a', NULL, 'b');
    concat_ws
    -----------
    a-=>b
    (1 row)

Leave a comment