1

I hit this question trying to answer one in SO. When I pass \1 as first argument to lpad(string text, length int [, fill text]) function, it looks like it interprets \1 as regexp substitution part looking for string to pad chars, but then interprets same \1 as text (it is literally - '\1') calculating its length to substract from second argument: lpad('aaa',5,'b') calculates 5-length('\1'), not 5-length('aaa').

So the question is - are we meant to use SQL string functions with regexp substitution part meta syntax? What are the restrictions? Eg: concat, format, repeat, substr understand it, while length, initcap, ascii interpret it as just text.

Now I see that length, initcap, ascii accept string as argument, while format, repeat accept text, but then ltrim(text... and lpad(text... seem to be mix - they "understand" regexp metasyntax to find position and then fail to "convert string to text" to count input length?..

Here an example:

t=# with a as (select 'a 2 b'::text s)
t-# select
t-#   regexp_replace(s,' (\d){1} ','')
t-# , regexp_replace(s,' (\d){1} ','\1') _regular
t-# , regexp_replace(s,' (\d){1} ',concat('.','\1','.')) concat_regular
t-# , regexp_replace(s,' (\d){1} ',length('.\1')::text) length_string
t-# , regexp_replace(s,' (\d){1} ',reverse('\1')) reverse_string
t-# , regexp_replace(s,' (\d){1} ',initcap('\1')) initcap_broken
t-# , initcap(regexp_replace(s,' (\d){1} ','\1')) initcap_orig
t-# , regexp_replace(s,' (\d){1} ',ltrim('\1','a')) ltrim_broken
t-# , regexp_replace(s,' (\d){1} ',lpad('\1',5,'.')) lpad_broken
t-# , regexp_replace(s,' (\d){1} ',ascii('\1')::text) ascii_broken
t-# , regexp_replace(s,' (\d){1} ',format('\1')::text) format_regular
t-# , regexp_replace(s,' (\d){1} ',repeat('\1',3)) repeat_regular
t-# from a
t-# ;
 regexp_replace | _regular | concat_regular | length_string | reverse_string | initcap_broken | initcap_orig | ltrim_broken | lpad_broken | ascii_broken | format_regular | repe
at_regular
----------------+----------+----------------+---------------+----------------+----------------+--------------+--------------+-------------+--------------+----------------+-----
-----------
 ab             | a2b      | a.2.b          | a3b           | a1\b           | a2b            | A2b          | a2b          | a...2b      | a92b         | a2b            | a222
b
(1 row)

Time: 0.420 ms
1

You got it all wrong.
As in any other function execution, the regexp function parameters are evaluated before the execution and not during the execution.

This are the replace strings you are actually using:

with a as (select 'a 2 b'::text s)
select
     ''                     as empty_string
    ,'\1'                   as _regular
    ,concat('.','\1','.')   as concat_regular
    ,length('.\1')::text    as length_string
    ,reverse('\1')          as reverse_string
    ,initcap('\1')          as initcap_broken
    ,'\1'                   as initcap_orig
    ,ltrim('\1','a')        as ltrim_broken
    ,lpad('\1',5,'.')       as lpad_broken
    ,ascii('\1')::text      as ascii_broken
    ,format('\1')::text     as format_regular
    ,repeat('\1',3)         as repeat_regular
from a

+--------------+----------+----------------+---------------+----------------+----------------+--------------+--------------+-------------+--------------+----------------+----------------+
| empty_string | _regular | concat_regular | length_string | reverse_string | initcap_broken | initcap_orig | ltrim_broken | lpad_broken | ascii_broken | format_regular | repeat_regular |
+--------------+----------+----------------+---------------+----------------+----------------+--------------+--------------+-------------+--------------+----------------+----------------+
|              | \1       | .\1.           | 3             | 1\             | \1             | \1           | \1           | ...\1       | 92           | \1             | \1\1\1         |
+--------------+----------+----------------+---------------+----------------+----------------+--------------+--------------+-------------+--------------+----------------+----------------+
|improve this answer|||||
  • yes - this is the source of the problem - using regexp meta syntax (that u can safely use in third regex_replace argument as argument to string function itself. partially it works. so I ask (because could not find anything in docs) is it meant to not be used – Vao Tsun Feb 8 '17 at 15:20
  • @VaoTsun - I don't get why do you think there is an issue. Could we discuss a specific use-case? – David דודו Markovitz Feb 8 '17 at 15:25
  • the guy (SO link in my question) used regexp meta syntax as argument to string functions, expecting it \1 to be interpreted as first substitution, not as two characters "\1" - and it partially worked. So I could not understand is it meant usage or not – Vao Tsun Feb 8 '17 at 15:28
  • The guys code did exactly what it supposed to do - evaluated ' EXT ' || lpad('\1', 4, '0') which resulted in ' EXT 00\1'. Only then the regex function was executed. – David דודו Markovitz Feb 8 '17 at 15:33
  • (And what is "meta syntax"?) – David דודו Markovitz Feb 8 '17 at 15:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.