CharIndex

2005
2008
2012
2014
2016
2017
2019
2022

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

CharIndex String Function

Use the CharIndex function to search for the index of the beginning of a phrase within an expression, optionally providing the index in expression to start the search.

Syntax

db.fx.CharIndex({pattern}, {expression}[, {startSearchPosition}])

Arguments

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

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

startSearchPosition: – Where in `expression` to start the search for `pattern`.

Returns

long or long?

(based on nullability of `expression`)

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 CharIndex 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, CharIndex in dbExpression always returns long or long?.

If CharIndex does not find pattern within expression, CharIndex returns 0.

Examples

Select Statement

Select the first index of an address's line2 in line1 (if 0, line1 starts with line2)

IEnumerable<long?> result = db.SelectMany(
		db.fx.CharIndex(dbo.Address.Line2, dbo.Address.Line1)
	)
	.From(dbo.Address)
	.Execute();

Where Clause

Select any address id where line1 starts with line2

IEnumerable<int> result = db.SelectMany(
		dbo.Address.Id
	)
	.From(dbo.Address)
	.Where(db.fx.CharIndex(dbo.Address.Line2, dbo.Address.Line1) == 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.CharIndex(dbo.Address.Line2, dbo.Address.Line1))
    .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(
        dbo.Address.AddressType,
        db.fx.CharIndex(dbo.Address.Line2, dbo.Address.Line1).As("first_index_of")
    )
    .From(dbo.Address)
    .GroupBy(
        dbo.Address.AddressType,
        db.fx.CharIndex(dbo.Address.Line2, dbo.Address.Line1)
    )
    .Execute();

Having Clause

Select a list of address values where line2 appears in line1 or the address has a line2 (either line2 appears in line1 where index will be 0 or greater, or it is not found and charindex returns 0).

IEnumerable<dynamic> values = db.SelectMany(
		dbo.Address.AddressType,
		dbo.Address.Line1,
		dbo.Address.Line2
	)
	.From(dbo.Address)
	.GroupBy(
		dbo.Address.AddressType,
		dbo.Address.Line1,
		dbo.Address.Line2
	)
	.Having(
		db.fx.CharIndex(dbo.Address.Line2, dbo.Address.Line1) >= 0
	)
	.Execute();