Substring

2005
2008
2012
2014
2016
2017
2019
2022

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

Substring String Function

Use the Substring function to return a part of a string.

Syntax

db.fx.Substring({expression}, {start}, {length})

Arguments

expression: – The value to take a portion from.

start: – Where in `expression` to start taking characters.

length: – The number of characters to take form `expression`.

Returns

string or string?

(based on nullability of `expression`, `start`, or `length`)

Examples

Select Statement

Select the two characters from product name, starting after the first character.

IEnumerable<string> result = db.SelectMany(
        db.fx.Substring(dbo.Product.Name, 1, 2)
    )
    .From(dbo.Product)
    .Execute();

Where Clause

Select any product id where the name starts with a single letter.

IEnumerable<int> result = db.SelectMany(
		dbo.Product.Id
	)
	.From(dbo.Product)
	.Where(db.fx.Substring(dbo.Product.Name, 2, 1) == " ")
	.Execute();

Order By Clause

Select a list of products, ordered by their name but ignoring the first character.

IEnumerable<Product> products = db.SelectMany<Product>()
	.From(dbo.Product)
	.OrderBy(db.fx.Substring(dbo.Product.Name, 2, db.fx.Len(dbo.Product.Name) - 1))
	.Execute();

Group By Clause

Select a list of address values grouped by address type and the first character of city.

IEnumerable<dynamic> values = db.SelectMany(
        db.fx.Count().As("count"),
        dbo.Address.AddressType,
        db.fx.Substring(dbo.Address.City, 2, 1).As("ignore_first_character")
    )
    .From(dbo.Address)
    .GroupBy(
        dbo.Address.AddressType,
        db.fx.Substring(dbo.Address.City, 2, 1)
    )
    .Execute();

Having Clause

Select a count of addresses grouped by address type and the first character of city, having a first character in city in the last half of the alphabet.

IEnumerable<dynamic> values = db.SelectMany(
        db.fx.Count().As("count"),
        dbo.Address.AddressType
    )
    .From(dbo.Address)
    .GroupBy(
        dbo.Address.AddressType,
        db.fx.Substring(dbo.Address.City, 1, 1)
    )
    .Having(
        db.fx.Substring(dbo.Address.City, 1, 1) > "M"
    )
    .Execute();