admin管理员组

文章数量:1356839

What is wrong with the following regexp_replace

select regexp_replace(
    '<P><STRONG>Random text.</STRONG></P><IMG src="hello.jpg">',
    '(<[/]?[A-Z]+)',
    lower('\1'),
    'g'
);

I would like it to return

<p><strong>Random text.</strong><p><img src="hello.jpg">

but it returns the input string:

<P><STRONG>Random text.</STRONG></P><IMG src="hello.jpg">

DB fiddle:

What is wrong with the following regexp_replace

select regexp_replace(
    '<P><STRONG>Random text.</STRONG></P><IMG src="hello.jpg">',
    '(<[/]?[A-Z]+)',
    lower('\1'),
    'g'
);

I would like it to return

<p><strong>Random text.</strong><p><img src="hello.jpg">

but it returns the input string:

<P><STRONG>Random text.</STRONG></P><IMG src="hello.jpg">

DB fiddle: https://dbfiddle.uk/-VEFpAgH

  • postgresql
Share Improve this question asked Mar 28 at 19:09 thebjornthebjorn 27.4k12 gold badges105 silver badges148 bronze badges 7
  • Your lower() call is evaluated before regexp_replace(), so it just folds to a \1 and that's what the function sees. You'd need something like 'lower(\1)' (lower called by the regex engine) for that to work. It sounds like you'd have to extract matches from your input string, call lower() outside, then replace the matched substring with the lowercased substring. – Zegarek Commented Mar 28 at 19:21
  • @Zegarek ah.. that makes sense. Is there any way to do what I want? (postgres newbie here - just switching from mysql...). 'lower(\1)' produces lower(<P)>lower(<STRONG)>... – thebjorn Commented Mar 28 at 19:24
  • Sorry, I meant you'd need something like lower() called from inside there, but there's no such thing. I guess you could also put together a long and ugly regex emulating lower() by conditionally replacing A with a, B with b and so on :) For ASCII html tags it's just ugly, for anything with accents it'd be hell – Zegarek Commented Mar 28 at 19:29
  • so... are you saying I'll need to write a stored function? – thebjorn Commented Mar 28 at 19:29
  • Not a stored function, you need 3 function calls in this query: one extracts matches, the other takes that in and runs the actual replace from the match, to lower(match). – Zegarek Commented Mar 28 at 19:32
 |  Show 2 more comments

2 Answers 2

Reset to default 2

Here's an example mirroring regexp_replace() signature, so you can call it exactly the same way, not just for this particular example.
All it does is inject and evaluate the lower() call around your replacement string ($3 parameter):

create function regexp_replace_lower(inout text,text,text,text default '')
immutable parallel safe strict as $function$
begin 
execute format( 'select $

                本文标签：
                postgresqlPostgres regexpreplace with lowerStack Overflow

                        版权声明：本文标题：postgresql - Postgres regexp_replace with lower - Stack Overflow 内容由网友自发贡献，该文观点仅代表作者本人，
                        转载请联系作者并注明出处：http://www.betaflare.com/web/1744017974a2576686.html，
                        本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。