SQL

MySQL query String contains

Quite simple actually: The % is a wildcard for any characters set (none, one or many). Do note that this can get slow on very large datasets so if your database grows you’ll need to use fulltext indices.

SQL SELECT WHERE field contains words

Rather slow, but working method to include any of words: If you need all words to be present, use this: If you want something faster, you need to look into full text search, and this is very specific for each database type.

Using group by on multiple columns

Group By X means put all those with the same value for X in the one group. Group By X, Y means put all those with the same values for both X and Y in the one group. To illustrate using an example, let’s say we have the following table, to do with who is attending what subject at … Read more

How can I do a FULL OUTER JOIN in MySQL?

You don’t have full joins in MySQL, but you can sure emulate them. For a code sample transcribed from this Stack Overflow question you have: With two tables t1, t2: The query above works for special cases where a full outer join operation would not produce any duplicate rows. The query above depends on the UNION set operator to remove duplicate rows introduced by the query pattern. … Read more

Efficiently convert rows to columns in sql server

There are several ways that you can transform data from multiple rows into columns. Using PIVOT In SQL Server you can use the PIVOT function to transform the data from rows to columns: See Demo. Pivot with unknown number of columnnames If you have an unknown number of columnnames that you want to transpose, then you can use dynamic SQL: See Demo. Using … Read more

Using group by on multiple columns

Group By X means put all those with the same value for X in the one group. Group By X, Y means put all those with the same values for both X and Y in the one group. To illustrate using an example, let’s say we have the following table, to do with who is attending what subject at … Read more

+ More