Is There Startswith or Contains in T SQL with Variables

Is there StartsWith or Contains in t sql with variables?

StartsWith

a) left(@edition, 15) = 'Express Edition'
b) charindex('Express Edition', @edition) = 1

Contains

charindex('Express Edition', @edition) >= 1

Examples

left function

set @isExpress = case when left(@edition, 15) = 'Express Edition' then 1 else 0 end

iif function (starting with SQL Server 2012)

set @isExpress = iif(left(@edition, 15) = 'Express Edition', 1, 0);

charindex function

set @isExpress = iif(charindex('Express Edition', @edition) = 1, 1, 0);

How to find if a string starts with a number and a bracket in SQL server?

SQL Server doesn't have great pattern matching but if you know that the numbers prefixing the ) will always be less then 500, you can do this:

DECLARE @myString VARCHAR(100) = '266) Some text'

If (PATINDEX('[0-9])%', @myString) > 0) OR         --Check for one digit
   (PATINDEX('[0-9][0-9])%', @myString) > 0) OR    --Check for two digits
   (PATINDEX('[0-9][0-9][0-9])%', @myString) > 0) --Check for three digits
begin
    print @myString
end

Entity Framework: StartsWith to SQL

The following EF Core issue tracker thread could shed some light on why it is implemented this way - Query: Improve translation of String's StartsWith, EndsWith and Contains #474 . Here are some important excerpts:

Linq translation for methods Contains, EndsWith and StartsWith that we have in the Relational package uses LIKE operator, which may return incorrect results if the value parameter (what we are searching for) contains wildcard characters, e.g. '%' or '_'.

and then

In general for cases in which LIKE doesn't work well we can fall back to alternative translations that don't rely on LIKE, e.g. for String.StartsWith():

var underscoreAThings = Things.Where(t => t.Name.StartsWith(t.Prefix));

SELECT * FROM Things WHERE CHARINDEX(Prefix, Name) = 1 OR Prefix='';

Note that CHARINDEX() won't match an empty string but String.StartsWith("") always return true, that's why we add the Prefix ='' condition.
The main disadvantage of this translation is that it is not sargable. That can be addressed with a hybrid translation, e.g.:

SELECT * FROM Things WHERE Name LIKE Prefix+'%' AND (CHARINDEX(Prefix, Name) = 1 OR Prefix = '');

Shortly, with the current translation they address SQL query sargeability as well as the CLR string.StartsWith method compatibility. In different phases of the EF Core development they used only first or only second approach, and finally get to this hybrid approach.

SQL like search string starts with

SELECT * from games WHERE (lower(title) LIKE 'age of empires III');

The above query doesn't return any rows because you're looking for 'age of empires III' exact string which doesn't exists in any rows.

So in order to match with this string with different string which has 'age of empires' as substring you need to use '%your string goes here%'

More on mysql string comparision

You need to try this

SELECT * from games WHERE (lower(title) LIKE '%age of empires III%');

In Like '%age of empires III%' this will search for any matching substring in your rows, and it will show in results.

Using variable in SQL LIKE statement

Joel is it that @SearchLetter hasn't been declared yet? Also the length of @SearchLetter2 isn't long enough for 't%'. Try a varchar of a longer length.

How to use parameters in contains method in store procedure

With CONTAINS, in order to pass a wildcard through with a variable, you need to make the wildcard a part of the variable.

The easiest way to do it within a stored procedure is adding a variable (or modifying your current variable).

For example,

DECLARE @nameX NVARCHAR(4000) = '"' + @name + '" OR "' + @name + '*"'
...
CONTAINS(name, @nameX)

Inner-join in sql with contains condition

SQLFiddle example for MySQL 5.5
SQLFiddle example for SQL

Table and data

create table table1 (id int, locations varchar(100));
insert into table1 values 
(1, 'India, Australia'),
(2, 'US, UK');

create table table2 (table2id int, location varchar(100));
insert into table2 values
(101, 'Italy'),
(102, 'UK'),
(103, 'Hungary'),
(104, 'India');

MySQL query

select
  table1.id,
  table2.table2id,
  table2.location,
  table1.locations
from table1
join table2 on table1.locations like concat('%', table2.location, '%')

SQL Server query

select
  table1.id,
  table2.table2id,
  table2.location,
  table1.locations
from table1
join table2 on table1.locations like '%' + table2.location + '%'

Edit

In case where US location is contained in the country name Australia, the above query may not work as desired. To work around that problem, here's a possible query to use

select
  table1.id,
  table2.table2id,
  table2.location,
  table1.locations
from table1
join table2 on 
  ',' + replace(table1.locations,', ', ',') + ',' like '%,' + table2.location + ',%'

This query forces India, Australia to become ,India,Australia,. This is then compared with ,US, and therefore will not suffer from incorrect results.

SQL - Query to find if a string contains part of the value in Column

The answer would be "use LIKE".

See the documentation: https://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html

You can do WHERE 'string' LIKE CONCAT(column , '%')

Thus the query becomes:

select * from t1 where 'ABCDEFG' LIKE CONCAT(column1,'%');

If you need to match anywhere in the string:

select * from t1 where 'ABCDEFG' LIKE CONCAT('%',column1,'%');

Here you can see it working in a fiddle:
http://sqlfiddle.com/#!9/d1596/4

linq to sql startwith performance indexed columns

So what you need to do here is take the value of a variable and use it as a constant in an Expression that you are generating. This is actually quite possible. What we'll need is an expression that accepts the parameter you want as the parameter of your real selector, as second parameter that is a placeholder for the constant value, and then the value that you want to be a constant. We can then replace all instances of the parameter with the value of the constant, leaving just a function that maps the real parameter to the result:

public static Expression<Func<TSource, TResult>> EmbedConstant
    <TSource, TResult, TConstant>(
    this Expression<Func<TSource, TConstant, TResult>> expression,
    TConstant constant)
{
    var body = expression.Body.Replace(
        expression.Parameters[1],
        Expression.Constant(constant));
    return Expression.Lambda<Func<TSource, TResult>>(
        body, expression.Parameters[0]);
}

This relies on the following methods for replacing all instances of one expression with another:

public static Expression Replace(this Expression expression,
    Expression searchEx, Expression replaceEx)
{
    return new ReplaceVisitor(searchEx, replaceEx).Visit(expression);
}
internal class ReplaceVisitor : ExpressionVisitor
{
    private readonly Expression from, to;
    public ReplaceVisitor(Expression from, Expression to)
    {
        this.from = from;
        this.to = to;
    }
    public override Expression Visit(Expression node)
    {
        return node == from ? to : base.Visit(node);
    }
}

This allows you to map this:

string search = "julien";
var list = db.Users.Where(x => x.Name.StartsWith(search));
string query = list.ToString();

Into this:

string search = "julien";
Expression<Func<User, string, bool>> predicate = 
    (item, searchTerm) => item.Name.StartsWith(searchTerm);
var list = db.Users.Where(predicate.EmbedConstant(search));
string query = list.ToString();

---

Related Topics