Reference > Functions > String

Replace

  • 2005, 2008, 2012, 2014, 2016, 2017, 2019, 2022

Replace String Function

Use the Replace function to replace all occurrences of a pattern with another string value.

Syntax

db.fx.Replace({expression}, {find}, {replacement})

Arguments

expression
The value that will have occurrences of `find` replaced with `replacement`.
find
The value to search for in `expression`.
replacement
The value to replace all occurrences of `find` in `expression`.

Returns

string or string?
(based on nullability of `expression`, `find`, or `replacement`)

Examples

Select Statement

Select street addresses replacing "St." with "Ave."

IEnumerable<string> result = db.SelectMany(
        db.fx.Replace(dbo.Address.Line1, "St.", "Ave.")
	)
	.From(dbo.Address)
	.Execute();

Where Clause

Select any product where replacing "Player" with "Play" changes the name.

IEnumerable<dynamic> result = db.SelectMany(
        dbo.Product.Id,
        dbo.Product.Name
    )
    .From(dbo.Product)
    .Where(db.fx.Replace(dbo.Product.Name, "Player", "Play") != dbo.Product.Name)
    .Execute();

Order By Clause

Select a list of persons, ordered by the removal of "Mr. " with an empty string.

IEnumerable<Person> persons = db.SelectMany<Person>()
    .From(dbo.Person)
    .OrderBy(db.fx.Replace(dbo.Person.LastName, "Mr. ", ""))
    .Execute();

Group By Clause

Select how many, and the name, of products resulting from replacing "Player" with "Play" in the name.

IEnumerable<dynamic> values = db.SelectMany(
        db.fx.Count().As("count"),
        dbo.Product.Name
    )
    .From(dbo.Product)
    .GroupBy(
        dbo.Product.Name,
        db.fx.Replace(dbo.Product.Name, "Player", "Play")
    )
    .Execute();

Having Clause

Select how many, and the name, of products resulting from replacing "Player" with "Play" in the name.

IEnumerable<dynamic> values = db.SelectMany(
        db.fx.Count().As("count"),
        dbo.Product.Name
    )
    .From(dbo.Product)
    .GroupBy(dbo.Product.Name)
    .Having(
        db.fx.Replace(dbo.Product.Name, "Play", "Player") != dbo.Product.Name
    )
    .Execute();
Previous
PatIndex

© 2024 dbExpression. All rights reserved.