using regexp substitution part in postgres string functions

Question

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

Answer 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円 |
+--------------+----------+----------------+---------------+----------------+----------------+--------------+--------------+-------------+--------------+----------------+----------------+

• Vao Tsun: 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
• David דודו Markovitz: @VaoTsun - I don't get why do you think there is an issue. Could we discuss a specific use-case?
• Vao Tsun: 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
• David דודו Markovitz: The guys code did exactly what it supposed to do - evaluated ' EXT ' || lpad('1円', 4, '0') which resulted in ' EXT 001円'. Only then the regex function was executed.
• David דודו Markovitz: (And what is "meta syntax"?)