Query using like operator

Question

Query using like operator

Navigation

#1 by (3 votes)
#2 by (1 votes)
#3 by (1 votes)
#4 by (0 votes)

5

In a query in the database I enter the keywords, month, and year. I want to get the data according to the key words, the month and the year. For example: the word school on the date 2015-08 .

For this, I use the like instead of = operator because the date is complete and I want to do the search per month in a given year. The same thing is for the keyword because I can have words other than the word school in the registry.

The query is:

select * from despesas where palavraChave like "%escola%" or data like '2015-08%'

The error is:

If I do not spend any date and no word, it brings me the data and I do not want to. I only want the data if one of the fields is found. I've already used !null , !notnull , things like, but it does not solve.

The language is sql and the server language is php.

sql

asked by anonymous 14.08.2015 / 16:11

4 answers

How do I expect everything to load before running a function? Problem with log4j configuration

score 3 · Answer 1

1) Verify whether variables have value or not before executing the query.

2) If it is not possible to do the verification, do the following sql

select * from despesas where (palavraChava like "%escola%" and "%escola%" <> "%%") or (data like '2015-08%' and '2015-08%' <> '%')

Explaining better:

Where I put "%escola%" <> "%%" is because I imagine that the way you mount the sql is as follows: palavraChave like "%{variavel}%" so if the variable is not filled the snippet will be "%%" <> "%%" .

The same logic goes into the date field.

score 1 · Answer 2

If I understand what you need, try this query.

SELECT * FROM despesas WHERE palavraChave LIKE '%escola%' AND Month(Data) = 8 AND Year(Data) = 2015

I've never seen LIKE in date field unless varchar . But even so, working with date in varchar field only gives a mess.

score 1 · Answer 3

You can instead use LIKE to retrieve a date in your database because DATETIME or DATE are strings , although it is more correct to use the commands used above by others.

You just need to do this

SELECT * FROM despesas WHERE palavraChave LIKE '%escola%' OR data LIKE '%2015-08%';

You need to use the % symbol before the year and after the month or if you want only the month, use before and after the month BUT in this case you would need to LIKE '%-08-%' , put the - before and after the month , otherwise it risks risking day and years that contain that value.

In this way he only picks up the month.

You do not need to use double quotes in text fields, single quotes are the most correct and most used.

If you use OR , it will return everything you have with the school keyword and any date and all that date to August 2015 and any key words, so if it is to do a search like this, it would be easier using only the date or keyword.

'SELECT * FROM despesas WHERE palavraChave LIKE '%escola%';'

or SELECT * FROM despesas WHERE data LIKE '%2015-08%';

Both SELECTS will return exactly the same as your query.

I believe you want your query to return the data where the keyword is school and the date is August 2015. Thus, use AND

'SELECT * FROM despesas WHERE palavraChave LIKE '%escola%' AND data LIKE '%2015-08%';'

score 0 · Answer 4

Analyzing your query:

select * from despesas where palavraChave like "%escola%" or data like '2015-08%'

You're telling SQL:

Please give me all the records of the table expenses , where wordChave is like "school" no matter the date, OR where / em> is like '2015-08' no matter the wordChave.

That is, you are asking too much - using AND , like this:

select * from despesas where palavraChave like '%escola%' AND data like '2015-08%'

You're saying:

Please give me all the records of the expenses table, where the wordChave is like "school" and the expense has the date appeared with "2015-08". The others (records with another wordChave and others on another date) are excluded.

SqlFiddle

Here's a SqlFiddle to demonstrate the difference - use a OR and then use a AND and Run SQL to see the difference.