Sql comparison operators: Difference between revisions

From wikinotes
(Created page with "= Regex = <source lang="mySQL"> ## Regex is treated fairly similarly wherever it is used ## (Even the different versions of regex). But for clarity here ## is the syntax that...")
 
m (Will moved page Sql: comparison operators to Sql comparison operators without leaving a redirect)
 
(4 intermediate revisions by the same user not shown)
Line 1: Line 1:
= Regex =
= AND/OR =
<source lang="mySQL">
<blockquote>
## Regex is treated fairly similarly wherever it is used
<syntaxhighlight lang="MySQL">
## (Even the different versions of regex). But for clarity here
SELECT * FROM users
## is the syntax that mySQL uses:
WHERE name = 'foo' OR name = 'bar'
##
</syntaxhighlight>
## .            --> any single character
</blockquote><!-- AND/OR -->
## [A-z][0-9]    --> character range
## [0-9]*a      --> Matches any or no occurrences of [0-9].
##                        Can be used for single chars and char ranges
## ^a            --> matches lines starting with 'a'
## a$            --> matches lines ending with 'a'
## ^aaa$        --> matches 'aaa'. not '*aaa*' (which is default in mySQL)


SELECT * FROM userTable WHERE name REGEXP 'ghis' # REGEX in sql query (implied '.*ghis.*'  
= IN =
                                                  # on either side unless anchored with '^', or '$')
<blockquote>
</source>
<syntaxhighlight lang="MySQL">
SELECT * FROM users WHERE name IN ('john', 'jane', 'iggy');
SELECT * FROM users WHERE name NOT IN ('john', 'jane', 'iggy');
</syntaxhighlight>
</blockquote><!-- IN -->


= Like =
= BETWEEN =
<source lang="mySQL">
<blockquote>
## _                --> any single character
<syntaxhighlight lang="MySQL">
## %                --> any or no instances of character
SELECT * FROM users WHERE age BETWEEN  30 AND 40;
</syntaxhighlight>
</blockquote><!-- BETWEEN -->


SELECT * FROM my_table  WHERE name LIKE 'ghis%'; # SQL LIKE in sql query
= REGEXP =
</source>
<blockquote>
<syntaxhighlight lang="MySQL">
'.'            # any single character
'[A-z][0-9]'    # character range
'[0-9]*a'      # Matches any or no occurrences of [0-9].
'^a'            # matches lines starting with 'a'
'a$'            # matches lines ending with 'a'
'^aaa$'        # matches 'aaa'. not '*aaa*' (which is default in mySQL)
'aaa'          # matches '.*aaa.*' unless anchored with '^' or '$'
</syntaxhighlight>
 
Example.
<syntaxhighlight lang="MySQL">
SELECT * FROM users WHERE name REGEXP '^Andr'
</syntaxhighlight>
</blockquote><!-- REGEXP -->
 
= LIKE =
<blockquote>
glob-style matching (<code>%</code> represents any or no characters).
 
<syntaxhighlight lang="MySQL">
SELECT * FROM users WHERE name LIKE 'Andr%';
</syntaxhighlight>
</blockquote><!-- LIKE -->

Latest revision as of 18:37, 19 September 2021

AND/OR

SELECT * FROM users
WHERE name = 'foo' OR name = 'bar'

IN

SELECT * FROM users WHERE name IN ('john', 'jane', 'iggy');
SELECT * FROM users WHERE name NOT IN ('john', 'jane', 'iggy');

BETWEEN

SELECT * FROM users WHERE age BETWEEN  30 AND 40;

REGEXP

'.'             # any single character
'[A-z][0-9]'    # character range
'[0-9]*a'       # Matches any or no occurrences of [0-9].
'^a'            # matches lines starting with 'a'
'a$'            # matches lines ending with 'a'
'^aaa$'         # matches 'aaa'. not '*aaa*' (which is default in mySQL)
'aaa'           # matches '.*aaa.*' unless anchored with '^' or '$'

Example. 

SELECT * FROM users WHERE name REGEXP '^Andr'

LIKE

glob-style matching (% represents any or no characters). 

SELECT * FROM users WHERE name LIKE 'Andr%';
Retrieved from "http://willpittman.net:8080/index.php?title=Sql_comparison_operators&oldid=27610"