SELECT with keyword search

Asked

Viewed 1,767 times

3

How to make a SELECT with specific fields and using LIKE %% in the same instruction? something similar to:
SELECT TABLE_A.COLUMN1, TABLE_A.COLUMN2 FROM TABLE_A WHERE COLUMN2 LIKE 'A%'
I’ve tried it but it returns nothing.

mysql

  • How are the data in your table? Give an example of what you want to return

  • have a table Administrator with the fields idAdm, userName,level and userPass, would like to do the research like this: SELECT Administrator.userPass, Administrator.userName FROM Administrator WHERE userName LIKE = "c%"; however it does not return, more if I remove the tables and place * normal stock.

  • Is there any user starting with c minuscule or is capital?

2 answers

4


The SELECT command doesn’t look bad. This is if you want to select records where COLUMN2 has values starting with the letter To maíuscula. The synthesis of LIKE is simple:

  • % is a wildcard. Serves to represent 0, 1 or N characters
  • _ is a placeholder. Serves to represent 1 and only 1 any character

When using the pattern To% we are conditioning the results to any value started by To, or just To

  • To OK
  • Andreia OK
  • André OK
  • macaw NÃO OK
  • Monkey NÃO OK

To ignore upper or lower case, assuming the table or field is set to be Case Sensitive, just reduce both to one of the cases, for example:

SELECT * FROM TABELA WHERE LOWER(COLUNA) LIKE 'a%'

The wildcard can be repeated. For example, if we want to restrict to names that contain a To in any position:

LIKE '%a%'

An email check (rudimentary):

LIKE '%_@_%._%'

With a minimum of 3 characters:

LIKE '___%'

And so on.

  • Just a suggestion Sergio, it is not easier to do direct LIKE 'a%' instead of calling the function LOWER?

  • 1

    It is. LOWER in the LIKE part only ensures normalization if it is being passed by variable. If for example "a" was specified by the user. In this case, you can omit the LIKE LOWER, but not the column.

  • I’m no expert in banking, but I think for each record it will perform the function to make the comparison, no? So I pointed this out

  • We can always normalize the value, even if variable, before passing it to the query construction, yes... I’ll edit the answer, just so I don’t leave that question to anyone.

0

Put the identifier Administrator.username in the WHERE, maybe that’s why, that it is not identifying right 

SELECT Administrator.userPass, Administrator.userName
FROM Administrator
WHERE Administrator.userName LIKE "c%"

  • This is not mandatory Lucas, as in this context there is only one table with the column userName does not become mandatory as it has no ambiguity.

  • I said that because I don’t know the whole context of the bank and their relationship, but somehow I always like to put it so I don’t have any problems.

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.