Mysql: which fields matched using "OR"

mysql match against
mysql like
mysql regexp
mysql contains
mysql rlike
mysql match any word in string
mysql pattern matching
mysql match against example

I have this query:

SELECT * FROM accounts WHERE username = "aaa" OR email = "abc@example.com" OR mobile = "123456789"

I can find which field did match like this:

var username = req.body.username;
var email = req.body.email;
var mobile = req.body.mobile;

database.query("SELECT * FROM accounts WHERE username = ? OR email = ? OR mobile = ?",[username, email, mobile, (err, result)=>{ 
        if (result.username == username) {...}
        if (result.email == email) {...}
        if (result.mobile == mobile) {...}
    }

But, I'd like to know which field did match without using if multiple times (if possible). So, is that possible form within Mysql only?

You can put it in the SELECT list. You can use CONCAT_WS() to combine the list of columns into a comma-separated list. It will omit NULL values, so this will just list the matched fields.

database.query(`SELECT *, 
    CONCAT_WS(',', 
        IF(username = ?, 'username', NULL), 
        IF(email = ?, 'email', NULL), 
        IF(mobile = ?, 'mobile', NULL)) AS matched_fields 
FROM accounts WHERE username = ? OR email = ? OR mobile = ?`, [username, email, mobile, username, email, mobile], (err, result) => {
  var matched = result.matched_fields.split(',');
  matched.each(field => {
    switch(field) {
    case 'username': ...; break;
    case 'email': ...; break;
    case 'mobile': ...; break;
  });
})

MySQL Tutorial :: 4.4.7 Pattern Matching, In MySQL, SQL patterns are case-insensitive by default. Some examples are shown here. Do not use = or <> when you use SQL patterns. Use the LIKE or� For example, if you were matching 3 different columns and wanted to more heavily weight certain columns: SELECT search.*, MATCH (name) AGAINST ('black' IN BOOLEAN MODE) AS name_match, MATCH (keywords) AGAINST ('black' IN BOOLEAN MODE) AS keyword_match, MATCH (description) AGAINST ('black' IN BOOLEAN MODE) AS description_match FROM search WHERE MATCH (name, keywords, description) AGAINST ('black' IN BOOLEAN MODE) ORDER BY (name_match * 3 + keyword_match * 2 + description_match) DESC LIMIT 0,100;

Create custom column in select query and that custom column has case statement with three column(username, email, mobile) matching and print respective flag in that custom column to let it know that row comes from based on which condition.

MySQL 8.0 Reference Manual :: 3.3.4.7 Pattern Matching, In MySQL, SQL patterns are case-insensitive by default. Some examples are shown here. Do not use = or <> when you use SQL patterns. Use the LIKE or� MATCH() function searches a string against a text collection (A set of one or more columns included in a FULLTEXT index.). For each row in the table, MATCH() returns a relevance value; that is, a similarity measure between the search string (given as the argument to AGAINST() function) and the text in that row in the columns named in the MATCH() list.

Try Below:-

SELECT * FROM accounts WHERE (username = "aaa" OR email = "abc@example.com" OR mobile = "123456789")

How the MATCH() Function Works in MySQL, In MySQL, the MATCH() function performs a full-text search. It accepts a comma separated list of table columns to be searched. The table/s� An alternative would be to use a LEFT OUTER JOIN and check for NULL. SELECT p.Name FROM pooltest p LEFT OUTER JOIN senttest s ON s.Name = p.Name WHERE s.Name IS NULL Note that the implicit join syntax you are using is considered obsolete and should be replaced with an explicit join.

Whichever field has first match then your condition become true and it will return result. If you want to match all the condition then you should use AND operator

MySQL Full text search, MATCH() function searches a string against a text collection (A set of one or more columns included in a FULLTEXT index.). For each row in the� First, use the UNION statement to combine rows in both tables; include only the columns that need to compare. The returned result set is used for the comparison. The returned result set is used for the comparison.

Mysql match on two columns and get count, In MYSQL how do I get a count on Names that match only if Month also match? IE Joe April should be only one share. The question(?) metacharacter is used to match zero (0) or one instances of the strings preceding it. SELECT * FROM `categories` WHERE `category_name` REGEXP 'com?'; will give all the categories containing string com .For Example, comedy , romantic comedy .

Perform Full-text Searches in MySQL (Part 2), In MySQL, natural-language full-text searches are performed using the The MATCH() function specifies the column where you want to search, In order to search the description field in full-text mode, we first have to create� To show all columns of a table, you use the following steps: Login to the MySQL database server. Switch to a specific database. Use the DESCRIBE statement.; The following example demonstrates how to display columns of the orders table in the classicmodels database.

MySQL Tutorial - MySQL By Examples for Beginners, A table is made up of columns (or fields) and rows (records). For strings, in addition to full matching using operators like '=' and '<>' , we can perform pattern � Change table_name and field to match your table name and field in question: UPDATE table_name SET field = REPLACE(field, 'foo', 'bar') WHERE INSTR(field, 'foo') > 0; REPLACE (string functions) INSTR (string functions) share. Share a link to this answer. Copy link.

Comments
  • I thought of that, but it is not different from using if() (in my code). So to imporve it, is it possible to put all the "AS" in an array to be returned as an array so I can just iterate that? something like AS matched[fieldname]
  • I've updated it to show how to get them all into a single column. SQL doesn't have arrays, so you have to use comma-separated strings.
  • that's not the question. He wants to know which condition caused the row to match.