Regex.Replace is translated to regexp_replace without the g flag — only the first match is replaced

Description

Regex.Replace(input, pattern, replacement) replaces all matches in .NET, but the translation produced by
NpgsqlRegexTranslator.TranslateReplace calls PostgreSQL regexp_replace without the g flag, which replaces only the first
match. The same LINQ query therefore returns different results when evaluated on the client and when translated to SQL.

TranslateOptions never emits g: for RegexOptions.None it returns p, and for RegexOptions.Singleline it returns an empty
string, so regexp_replace gets only three arguments — in both cases without g. (Current main,
src/EFCore.PG/Query/ExpressionTranslators/Internal/NpgsqlRegexTranslator.cs.)

Repro

public class Contact
{
    public int Id { get; set; }
    public string Phone { get; set; } = "";
}

// seed: new Contact { Phone = "+48 (600) 123-456" }

var hits = await db.Contacts
    .Where(c => Regex.Replace(c.Phone, "[^0-9]", "").Contains("600123456"))
    .ToListAsync();

Expected: one row — in .NET Regex.Replace("+48 (600) 123-456", "[^0-9]", "") is "48600123456".

Actual: no rows. Generated SQL:

SELECT c."Id", c."Phone"
FROM "Contacts" AS c
WHERE regexp_replace(c."Phone", '[^0-9]', '', 'p') LIKE '%600123456%'

With RegexOptions.Singleline the flags argument is dropped entirely, still without g:

WHERE regexp_replace(c."Phone", '[^0-9]', '') = ''

regexp_replace('+48 (600) 123-456', '[^0-9]', '', 'p') returns '48 (600) 123-456' — only the leading + is removed.

Suggested fix

Append g to the flags passed to regexp_replace (and pass the flags argument also when the translated options are otherwise empty),
e.g. regexp_replace(input, pattern, replacement, 'pg') for RegexOptions.None and regexp_replace(input, pattern, replacement, 'g')
for RegexOptions.Singleline. Both translated overloads — static Regex.Replace(input, pattern, replacement) and
Regex.Replace(input, pattern, replacement, options) — always replace every match in .NET, so g applies unconditionally.

Workaround

A user-defined function mapped with HasTranslation to regexp_replace(..., 'g').

Versions

Npgsql.EntityFrameworkCore.PostgreSQL 10.0.3 (also present on main)
PostgreSQL 17.6, .NET 10 (EF Core 10)

AI Triage

The below is an AI-generated analysis and may contain inaccuracies.

Confirmed bug; issue type set to Bug. Reproduced the original predicate returning no rows, and reduced it to a projection that directly compares server and .NET replacement results. Tested against PostgreSQL 18.4.

Npgsql EF provider	Minimized projection result
9.0.4	Correct: `48600123456`; replacement evaluated on the client
10.0.0, 10.0.2, 10.0.3	Incorrect: `48 (600) 123-456`; replacement evaluated on the server
11.0.0-rc.1.1	Same incorrect result
Current main (b038505)	Same incorrect result

Regression assessment: This is not new in 10.0.3. The missing flag was already present when translation was introduced by #3062, first released in 10.0.0-preview.1. It is a behavioral regression for projections previously evaluated correctly on the client. The original Where query is a different case: on 9.0.4 it throws a translation exception, rather than returning the expected row, because Regex.Replace translation was not supported.

Root cause: TranslateReplace omits g. The default overload emits:

SELECT regexp_replace(c."Phone", '[^0-9]', '', 'p')
FROM "Contacts" AS c
LIMIT 2

Also independently reproduced the four-argument .NET overload with RegexOptions.Singleline: it emits the three-argument PostgreSQL function and returns the same incorrect value. Direct SQL with flags pg and g, respectively, returns the expected 48600123456.

The suggested fix is appropriate: unconditionally append g inside TranslateReplace, including when the other flags are empty. Do not add it to the shared TranslateOptions, which is also used by Regex.Count. The existing replacement tests use anchored patterns such as ^A, which do not exercise multiple replacements with the test data. Coverage should include multiple matches for the default overload and explicit options, particularly None and Singleline.

Provider scope: This is an Npgsql translation bug. With SQLite 10.0.3, the minimized projection returns the correct result by evaluating Regex.Replace on the client; the original predicate throws a translation exception. SQLite therefore does not provide an equivalent server-side translation comparison, but does not exhibit the incorrect replacement result.

Duplicates: No likely duplicate found among open or closed issues in dotnet/efcore or this repository. #3059 and #3062 are the original feature request and implementation, not duplicate bug reports.

minimal repro

Use a .NET 10 console project referencing Npgsql.EntityFrameworkCore.PostgreSQL 10.0.3. Set Test__Npgsql__DefaultConnection to a disposable database: this program deletes and recreates it.

using System;
using System.Linq;
using System.Text.RegularExpressions;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;

await using var db = new TestContext();
await db.Database.EnsureDeletedAsync();
await db.Database.EnsureCreatedAsync();
db.Contacts.Add(new Contact { Phone = "+48 (600) 123-456" });
await db.SaveChangesAsync();

var actual = await db.Contacts
    .Select(c => Regex.Replace(c.Phone, "[^0-9]", ""))
    .SingleAsync();
var expected = Regex.Replace("+48 (600) 123-456", "[^0-9]", "");

if (actual != expected)
{
    throw new Exception($"Expected '{expected}', got '{actual}'.");
}

public class TestContext : DbContext
{
    public DbSet<Contact> Contacts => Set<Contact>();

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseNpgsql(Environment.GetEnvironmentVariable("Test__Npgsql__DefaultConnection"))
            .LogTo(Console.WriteLine, LogLevel.Information)
            .EnableSensitiveDataLogging();
}

public class Contact
{
    public int Id { get; set; }
    public string Phone { get; set; } = "";
}

Fails with:

System.Exception: Expected '48600123456', got '48 (600) 123-456'.

Thanks, fixing this for 10.0.4.

Navigation Menu

Repository navigation

Regex.Replace is translated to regexp_replace without the g flag — only the first match is replaced #4035

Description

Repro

Suggested fix

Workaround

Versions

AI Triage

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Regex.Replace is translated to regexp_replace without the g flag — only the first match is replaced #4035

Description

Description

Repro

Suggested fix

Workaround

Versions

Activity

roji commented on Oct 19, 2026

AI Triage

roji commented on Oct 19, 2026

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions