I have just started my journey with mySQL.

Unfortunately I couldn't find the answer for this question anywhere.

Could someone explain me using '.*' within SQL queries like in this one: WHERE city REGEXP '^[aeiou].* [aeiou]$'.

In what cases I could also use '.*'? Is it only intended for mySQL RDBS?

I would really appreciate your answers.

Thank you guys!

CodePudding user response：

I've never seen this written out, so here goes:

SQL Wildcard characters

In SELECT

In SELECT clauses, SELECT * means Select all columns from all tables in the query. SELECT mytable.* means Select all columns from mytable. In production software it's best to avoid these * column-name wildcards, instead putting an exact list of the needed columns.

In WHERE ... LIKE

In something LIKE 'other_thing' expressions in WHERE clauses, you can use

• % to represent 0 or more of any characters. % is like .* in regular expressions. But LIKE doesn't use a regular expression scheme, it uses its own scheme. So, for example, WHERE name LIKE 'Marcin%' finds all names starting with Marcin.

• _ matches exactly one character. _ is like . in regular expressions. For example, WHERE name LIKE 'Marc_n' finds Marcin, Marcon, and Marc n (with a space).

In WHERE clauses, the * character has no special meaning.

In regular-expression WHERE clauses

Some makes and versions of database server, but not all, offer regular-expression WHERE-filtering. This filtering feature isn't standardized for all SQL like LIKE 'whatever%'.

In WHERE something RLIKE '^Marcin.*$' (regexp matching) follows the particular database software products regexp rules.

CodePudding user response：

city REGEXP '^[aeiou].* [aeiou]$'. is an example of using a Regular Expression to search/match a complex pattern in text. The term 'Regular Expressions' refer to a technique where an expression (a sequence of symbols)formed following a defined syntax to describe these complex patterns. When the pattern we look for (say, in a column) is simple, we can use SQL operators like =, LIKE (with wildcard characters: %, and _); an example of this is find all rows where city begins with 'NEW':

city LIKE 'NEW%'

But if the pattern to find is complex, the LIKE operator may be insufficient, as in your example: `find all rows where city begins with one of these letters:a,e,i,o,u, followed by one-or more characters and a space, and ends with one of these characters: a,e,i,o,u. In such instances we can use 'Regular Expressions', but only if they are supported by the database product you are using. It appears that mysql supports them using this syntax. Other database products may not support Regular Expressions, or they might have different syntax for using them.

As for what the regular expression: .* does: . matches any single character, and * matches zero or more characters (any character). In short .* would mean 'at least one character` (a bit useless by itself, but it would have uses in a different context).

There are many websites teaching and allowing you to experiment with different regular expressions. I suggest that you have a look at what is available (this is a nice one: https://www.regular-expressions.info/tutorial.html), and use them only if your use-case requires very elaborate pattern matching. Try to use the ordinary SQL operators like LIKE, other comparison operators and string functions (like length, substring, etc), before using Regular Expressions, because the former are more likely to be optimised by your rdbms vendor.