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".
$ 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”
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.
Is that what these are for? MySQL migration? just wondering on the… purpose behind them…
It makes butterflies in our poor programmers life 🙂
Can “concat_ws” be used as an aggregate function?
i doubt, but there is no point: https://www.depesz.com/2010/02/17/waiting-for-9-0-string_agg/
Thanks. Missed that post, can’t wait for 9.0 to come out.
small formatting problem between section about concat_ws and left function 😉 Perhaps unclosed
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.
Comments are closed.