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...")
 
No edit summary
Line 1: Line 1:
= Regex =
= REGEXP =
<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
'.             # any single character
## is the syntax that mySQL uses:
'[A-z][0-9]'   # character range
##
'[0-9]*a'       # Matches any or no occurrences of [0-9].
## .            --> any single character
''              #       Can be used for single chars and char ranges
## [A-z][0-9]    --> character range
'^a'           # matches lines starting with 'a'
## [0-9]*a      --> Matches any or no occurrences of [0-9].  
'a$'           # matches lines ending with 'a'
##                        Can be used for single chars and char ranges
'^aaa$'         # matches 'aaa'. not '*aaa*' (which is default in mySQL)
## ^a            --> matches lines starting with 'a'
'aaa'          # matches '.*aaa.*' unless anchored with '^' or '$'
## a$            --> matches lines ending with 'a'
</syntaxhighlight>
## ^aaa$        --> matches 'aaa'. not '*aaa*' (which is default in mySQL)


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


= Like =
= LIKE =
<source lang="mySQL">
<blockquote>
## _                --> any single character
glob-style matching (<code>%</code> represents any or no characters).
## %               --> any or no instances of character


SELECT * FROM my_table  WHERE name LIKE 'ghis%'; # SQL LIKE in sql query
<syntaxhighlight lang="MySQL">
</source>
SELECT * FROM users WHERE name LIKE 'Andr%';
</syntaxhighlight>
</blockquote><!-- LIKE -->

Revision as of 17:58, 19 September 2021

REGEXP

'.              # any single character
'[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)
'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=27581"