PatIndex

2005
2008
2012
2014
2016
2017
2019
2022

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

PatIndex (Pattern Index) String Function

Use the PatIndex function to search for the starting position of a pattern in a string expression or zero if the pattern is not found in the string expression.

Syntax

db.fx.PatIndex({pattern}, {expression})

Arguments

pattern: – The value to search for in `expression`.

expression: – The element to search for `pattern`.

Returns

long or long?

(based on nullability of `expression`)

dbExpression does not include or append any wildcard characters with the provided pattern - it is up to you to provide the wildcard characters.

See String Operators (Microsoft)

Microsoft SQL Server returns bigint if expression is of the varchar(max), varbinary(max), or nvarchar(max) data types; otherwise, int. dbExpression generally maps these to CLR types long and int respectively. As the dbExpression implementation for PatIndex works with the CLR type string, there is no way to effect a different return type based on the length of the provided string for the expression method parameter. Therefore, PatIndex in dbExpression always returns long or long?.

Examples

Select Statement

Select the index of an occurrence of the state in the city name.

IEnumerable<long> result = db.SelectMany(
		db.fx.PatIndex("%" + dbo.Address.State + "%", dbo.Address.City)
	)
	.From(dbo.Address)
	.Execute();

Where Clause

Select a list of address ids where the value of city is equal to the value of line 1 (a wildcard token has not been added to the beginning or the end of 'dbo.Address.Line1', so PatIndex will return 1 only if the city exactly matches the line 1 of address).

IEnumerable<int> result = db.SelectMany(
		dbo.Address.Id
	)
	.From(dbo.Address)
	.Where(db.fx.PatIndex(dbo.Address.Line1, dbo.Address.City) == 1)
	.Execute();

Order By Clause

Select a list of addresses, ordered by the index of the occurrence of line2 in line1.

IEnumerable<Address> addresses = db.SelectMany<Address>()
    .From(dbo.Address)
    .OrderBy(db.fx.PatIndex("%" + dbo.Address.State + "%", dbo.Address.City))
    .Execute();

Group By Clause

Select a list of address values grouped by address type and the index of line2 in line1.

IEnumerable<dynamic> values = db.SelectMany(
		db.fx.Count().As("count"),
		dbo.Address.AddressType
	)
	.From(dbo.Address)
	.GroupBy(
		dbo.Address.AddressType,
		db.fx.PatIndex("%" + dbo.Address.State + "%", dbo.Address.City)
	)
	.Execute();

Having Clause

Select a the count of addresses grouped by address type and the index of state in line1 where the value of state appears in line1.

IEnumerable<dynamic> values = db.SelectMany(
		db.fx.Count().As("count"),
		dbo.Address.AddressType
	)
	.From(dbo.Address)
	.GroupBy(
		dbo.Address.AddressType,
		db.fx.PatIndex("%" + dbo.Address.State + "%", dbo.Address.Line1)
	)
	.Having(
		db.fx.PatIndex("%" + dbo.Address.State + "%", dbo.Address.Line1) > 0
	)
	.Execute();