MySQL Search Query Using LIKE

Question

I'm trying to build a query that takes some input parameters. Few things to note...

Not all parameters are required to be passed to the query.
If a parameter is not going to be passed to the query, must it still be passed anyway but as NULL?
If a parameter is not passed, how would I build it to not include the parameter?

So say you have...

IN name VARCHAR(30),
IN age VARCHAR(2),
IN address VARCHAR(50)

And you only want to search by name...

SELECT * FROM Table WHERE 
(
NAME LIKE CONCAT(name, '%')
);

This doesn't seem to work because the age wasn't passed in. Or what if the name was passed in and the address but not the age? How would I build that?

dazedandconfused · Accepted Answer · 2013-05-31 15:34:06Z

1

As a previous poster state, pass NULL for the unused parameters. Then structure your query similar to...

SELECT * FROM Table WHERE 
(
  ((nameParameter IS NULL) OR (NameColumn LIKE '%' + NameParameter + '%'))
  AND 
  ((ageParameter IS NULL) OR (AgeColumn = ageParameter))
  AND 
  ((addressParameter IS NULL) OR (AddressColumn = addressParameter))
)

answered May 31, 2013 at 15:34

dazedandconfused

3,2162 gold badges20 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 11:50:18Z

1

The issue is optinal parameters Cannot be used in MYSQL.

The workaround is to pass null value for parameter and inside the stored procedure use IF statement to handle the different situations.

Take a look here:

Writing optional parameters within stored procedures in MySQL?

edited May 23, 2017 at 11:50

CommunityBot

11 silver badge

answered May 31, 2013 at 15:27

S Nash

2,5293 gold badges39 silver badges71 bronze badges

Comments

Gimmy · Accepted Answer · 2013-05-31 15:25:43Z

0

Try this:

SELECT * FROM Table
WHERE name LIKE '%nametofind%';

answered May 31, 2013 at 15:25

Gimmy

3,9192 gold badges21 silver badges28 bronze badges

Collectives™ on Stack Overflow

MySQL Search Query Using LIKE

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related