Search code examples
sql-serverwildcardsql-like

SQL LIKE with different wild cards pattern

I have a form to get user input. In the Name textbox field user will specify how does he want the WILD Card implementation. For example if he types abc query should be:

Select * from Student where Name Like '%' '@Name' + '%'

If he types abc* the * should tell the query to return rows having name that start from abc

Select * from Student where Name Like '@Name' + '%'

If he types *abc the * should tell the query to return rows having name that ends with abc

Select * from Student where Name Like '%' + '@Name'

How can I write a query that would handle this type of user inputs? Will Regex help?

Solution

  • try this

    DECLARE @Name AS VARCHAR(100)='abc*'

if (CHARINDEX('*', @Name)>1)
  set @Name=Replace(@Name, '*', '%')
else 
  set @Name='%'+@Name+'%'

Declare @sqlText varchar(max)= "Select * from Student where Name Like '" + Replace(userinputtext, "*", "%") + "'";
Exec(@sqlText)