whole word 查询
where'$'+ts.TargetSegment+'$' LIKE '%[^a-z0-9]a[^a-z0-9]%'
charindex 解释
CHARINDEX ( expression1 , expression2 [ , start_location ] )
Expression1是要到expression2中寻找的字符中,start_location是CHARINDEX函数开始在expression2中找expression1的位置。
CHARINDEX函数返回一个整数,返回的整数是要找的字符串在被找的字符串中的位置。假如CHARINDEX没有找到要找的字符串,那么函数整数“0”。
1 查找左右是空格与自己
LINQ 写法
segmentsProjectTaskSQL.Where(t =>
t.ts.TargetSegment == searchTarget
|| (t.ts.TargetSegment.Contains(" " + searchTarget + " ")
|| t.ts.TargetSegment.StartsWith(searchTarget + " ")
|| t.ts.TargetSegment.EndsWith(" " + searchTarget))
);
生成的SQL
and (( CAST(charindex(N' '+'a'+' ', ts.TargetSegment) AS int)) > 0 or ts.TargetSegment = 'a')
2 查找左右是符号,空格与自己
charindex不支持通配符,所以使用 patindex
var query = db.Products
.Where( p => SqlFunctions.PatIndex("%[^a-z0-9]a[^a-z0-9]%", "$"+ts.TargetSegment+"$") > 0 );
生成SQL
and (( CAST(patindex(N'%[^a-z0-9]a[^a-z0-9]%', '$' +ts.TargetSegment+'$') AS int)) > 0 or ts.TargetSegment = 'a')