Access SQL Like * alternative in MS SQL server

Question

I'm working on a project that has a search engine. AS we know in MS ACCESS we could use "*" in Queries under Criteria field to retrieve all records. In SQL Server I need the same technique. I have tried different LIKE with WHERE Clauses. But I still didn't get the exact result I want.

In this project I have 3 textboxes (Category, Item, Location). If the user leaves any of them empty. I want to retrieve all the records.

I need something like this:

  string t1,t2,t3;
    if(!String.IsNullOrEmpty(txtCategory.Text))
    t1=txtCategory.Text;
    else
    t1="*";
    if(!String.IsNullOrEmpty(txtItem.Text))
    t2=txtItem.Text;
    else
    t2="*"
    if(!String.IsNullOrEmpty(txtLoc.Text))
    t2=txtLoc.Text;
    else
    t3="*";
    -
    -
    -
    // in a function i have this :

    SELECT * FROM Table_Items WHERE Category='"+t1+"' AND Item='"+t2+"' AND Location='"+t3+"'"

Possible duplicate of [Conditional where clause in Sql Server?](http://stackoverflow.com/questions/18629132/conditional-where-clause-in-sql-server) — Tab Alleman, Jun 27 '16 at 13:33
Please read up on [SQL Injection](http://www.w3schools.com/sql/sql_injection.asp). — TGnat, Jun 27 '16 at 13:34

score 1 · Answer 1 · answered Jun 27 '16 at 13:39

1

Change your * to the %

... Where Category Like "'+t1+"' and Item Like '"+t2+"' ...

answered Jun 27 '16 at 13:39

John Cappelletti

79,615
7
44
66

score 1 · Accepted Answer · answered Jun 27 '16 at 13:43

1

in ms sql server you can use the same technique but instead of * you should use %.

for examples:

%: means any

a%: all strings that start with a

%z: all strings that end with z

SO, your code should look like something as below:

// codes here

t3="%";

WHERE ColumnName LIKE t3
or
Where ColumnName LIKE '%'

I hope that will help you.

answered Jun 27 '16 at 13:43

reaz

735
1
10
20

1

Do note: MS Access SQL does support `%` wildcard but you must use the `ALIKE` operator (for ANSI-LIKE). – Parfait Jun 28 '16 at 00:27

Access SQL Like * alternative in MS SQL server

2 Answers2