Waiting for 9.1 – concat, concat_ws, right, left, reverse

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.

10 thoughts on “Waiting for 9.1 – concat, concat_ws, right, left, reverse”

  1. 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.

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

    
				
    

							
    
		
    8. 
		
  8. 
			
		
    9. 
		
  9. 
			
    
				

				
    
					
    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
    
				
    

							
    
		
    10. 
		
  10. 
			
    
				

				
    
					
    @JSpatz:
    



    # SELECT concat('a', NULL, 'b');
 concat
--------
 ab
(1 ROW)
    





    # SELECT concat_ws('-=>', 'a', NULL, 'b');
 concat_ws
-----------
 a-=>b
(1 ROW)
    


				
    

							
    
		
    11. 
		


		
	
		
Comments are closed.