How to use LIKE and IN operator at the same like ...name LIKE IN ('alex%','raj%','rag%')?

Question

I want to prepare a query like:

SELECT name 
FROM Employee 
WHERE name LIKE IN ('alex%','raj%','rag%') 

Is it possible in SQL Server ?

Answer 1

Is it possible in SQL Server ?

No it is not possible.

As a solution to your problem you need to use OR like

WHERE name LIKE 'alex%' OR name LIKE  'raj%' OR name LIKE 'rag%'

On a side note:

Also you can have a trick like, lets say you want to match using the first three characters of your column then it would be like

WHERE LEFT(name, 3) IN ('ale', 'raj', 'rag')

Answer 2

You need to use OR instead of IN operator to do this

SELECT name FROM Employee 
WHERE name LIKE 'alex%' OR name LIKE  'raj%' OR name LIKE 'rag%'

Answer 3

Alternative of using combination of OR and LIKE is implementing a CLR regular expression function as there is not native T-SQL regular expression support.

You can use the following function for checking if given string matches a value:

/// <summary>
///    Indicates whether the regular expression specified in the Regex constructor finds a match in a specified input string.
/// </summary>
/// <param name="sqlPatern">Regular expression</param>
/// <param name="sqlValue">The string to search for a match.</param>
/// <returns></returns>
[SqlFunction(DataAccess = DataAccessKind.None, IsDeterministic = true)]
public static SqlBoolean IsMatch(SqlString sqlPatern, SqlString sqlValue)
{
    if (sqlPatern.IsNull || sqlValue.IsNull)
    {
        return new SqlBoolean(false);
    }
    else
    {
        Regex rgx = new Regex(sqlPatern.Value);
        return new SqlBoolean(rgx.IsMatch(sqlValue.Value));
    }
}

and you can check here instructions of how to deploy a CLR function in the SQL Server.

Then your issue can be solved like this:

SELECT name 
FROM Employee 
WHERE [dbo].[fn_Utils_RegexIsMatch] ('^alex.*|^raj.*^rag.*', name) = 1

Of course, you can now use the regex power for filtering :-)

Answer 4

sql select query

SELECT name FROM Employee 
WHERE name LIKE 'alex' OR name LIKE  'raj' OR name LIKE 'rag';

Answer 5

can be in this way:

declare @t table (value varchar (20))
insert into @t VALUES ('ale')
insert into @t VALUES ('raj')
insert into @t VALUES ('rag')

declare @your_table table (your_columnname varchar (255))

insert into @your_table VALUES ('ale')

select * from @your_table where your_columnname in (select value from @t)