Regular expression syntax cheatsheet This page provides an overall cheat sheet of all the capabilities of RegExp syntax by aggregating the content of the articles in the RegExp guide. If you need more information on a specific topic, please follow the link on the corresponding heading to access the full article or head to the guide. Basic syntax of Regular Expression Example:- all the table1 t1 that have the word X1 in them. It does not matter whether the 'X1' is at the beginning, middle or end of the title.

Skip to end of metadataGo to start of metadata

GROUP BY; SORT/ORDER/CLUSTER/DISTRIBUTE BY; JOIN (Hive Joins, Join Optimization, Outer Join Behavior); UNION; TABLESAMPLE; Subqueries; Virtual Columns; Operators and UDFs; LATERAL VIEW; Windowing, OVER, and Analytics; Common Table Expressions

Select Syntax

• A SELECT statement can be part of a union query or a subquery of another query.
• table_reference indicates the input to the query. It can be a regular table, a view, a join construct or a subquery.
• Table names and column names are case insensitive.
  • In Hive 0.12 and earlier, only alphanumeric and underscore characters are allowed in table and column names.
  • In Hive 0.13 and later, column names can contain any Unicode character (see HIVE-6013). Any column name that is specified within backticks (`) is treated literally. Within a backtick string, use double backticks (``) to represent a backtick character.
  • To revert to pre-0.13.0 behavior and restrict column names to alphanumeric and underscore characters, set the configuration property hive.support.quoted.identifiers to none. In this configuration, backticked names are interpreted as regular expressions. For details, see Supporting Quoted Identifiers in Column Names (attached to HIVE-6013). Also see REGEX Column Specification below.

• Simple query. For example, the following query retrieves all columns and all rows from table t1.

  Note

  As of Hive 0.13.0, FROM is optional (for example, SELECT 1+1).

• To get the current database (as of Hive 0.13.0), use the current_database() function:

• To specify a database, either qualify the table names with database names ('db_name.table_name' starting in Hive 0.7) or issue the USE statement before the query statement (starting in Hive 0.6).

  'db_name.table_name' allows a query to access tables in different databases.

  USE sets the database for all subsequent HiveQL statements. Reissue it with the keyword 'default' to reset to the default database.

WHERE Clause

The WHERE condition is a boolean expression. For example, the following query returns only those sales records which have an amount greater than 10 from the US region. Hive supports a number of operators and UDFs in the WHERE clause:

As of Hive 0.13 some types of subqueries are supported in the WHERE clause.

ALL and DISTINCT Clauses

The ALL and DISTINCT options specify whether duplicate rows should be returned. If none of these options are given, the default is ALL (all matching rows are returned). DISTINCT specifies removal of duplicate rows from the result set. Note, Hive supports SELECT DISTINCT * starting in release 1.1.0 (HIVE-9194).

ALL and DISTINCT can also be used in a UNION clause – see Union Syntax for more information.

Partition Based Queries

In general, a SELECT query scans the entire table (other than for sampling). If a table created using the PARTITIONED BY clause, a query can do partition pruning and scan only a fraction of the table relevant to the partitions specified by the query. Hive currently does partition pruning if the partition predicates are specified in the WHERE clause or the ON clause in a JOIN. For example, if table page_views is partitioned on column date, the following query retrieves rows for just days between 2008-03-01 and 2008-03-31.

If a table page_views is joined with another table dim_users, you can specify a range of partitions in the ON clause as follows:

• See also Partition Filter Syntax.
• See also Group By.
• See also Sort By / Cluster By / Distribute By / Order By.

HAVING Clause

Hive added support for the HAVING clause in version 0.7.0. In older versions of Hive it is possible to achieve the same effect by using a subquery, e.g:

can also be expressed as

LIMIT Clause

The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement.

LIMIT takes one or two numeric arguments, which must both be non-negative integer constants.

The first argument specifies the offset of the first row to return (as of Hive 2.0.0) and the second specifies the maximum number of rows to return.

When a single argument is given, it stands for the maximum number of rows and the offset defaults to 0.

The following query returns 5 arbitrary customers

The following query returns the first 5 customers to be created

The following query returns the 3rd to the 7th customers to be created

REGEX Column Specification

A SELECT statement can take regex-based column specification in Hive releases prior to 0.13.0, or in 0.13.0 and later releases if the configuration property hive.support.quoted.identifiers is set to none.

• We use Java regex syntax. Try http://www.fileformat.info/tool/regex.htm for testing purposes.
• The following query selects all columns except ds and hr.

More Select Syntax

See the following documents for additional syntax and features of SELECT statements:

• JOIN

Found this super handy cheat sheet for regular expressions.

Also, this serves as a useful reminder when returning to regular expressions.
Regular expressions syntax

Regular Expression	Will match…
foo	The string “foo”
^foo	“foo” at the start of a string
foo$	“foo” at the end of a string
^foo$	“foo” when it is alone on a string
[abc]	a, b, or c
[a-z]	Any lowercase letter
[^A-Z]	Any character that is not a uppercase letter
(gif|jpg)	Matches either “gif” or “jpeg”
[a-z]+	One or more lowercase letters
[0-9.-]	Any number, dot, or minus sign
^[a-zA-Z0-9_]{1,}$	Any word of at least one letter, number or _
([wx])([yz])	wy, wz, xy, or xz
[^A-Za-z0-9]	Any symbol (not a number or a letter)
([A-Z]{3}|[0-9]{4})	Matches three letters or four numbers

PHP regular expression functions

Function	Description
preg_match()	The preg_match() function searches string for pattern, returning true if pattern exists, and false otherwise.
preg_match_all()	The preg_match_all() function matches all occurrences of pattern in string.
preg_replace()	The preg_replace() function operates just like ereg_replace(), except that regular expressions can be used in the pattern and replacement input parameters.
preg_split()	The preg_split() function operates exactly like split(), except that regular expressions are accepted as input parameters for pattern.
preg_grep()	The preg_grep() function searches all elements of input_array, returning all elements matching the regexp pattern.
preg_quote()	Quote regular expression characters

Validate domain name

Verify if a string is a valid domain name.

Highlight a word from a text

Highlight search results in your WordPress blog

Open your search.php file and find the the_title() function. Replace it with the following:

Save the search.php file and open style.css. Append the following line to it:*/

Get all images from a HTML document

Remove repeated words (case insensitive)

Remove repeated punctuation

Matching a XML/HTML tag

This simple function takes two arguments: The first is the tag you’d like to match, and the second is the variable containing the XML or HTML. Once again, this can be very powerful used along with cURL.

Matching an XHTML/XML tag with a certain attribute value

This function is very similar to the previous one, but it allow you to match a tag having a specific attribute. For example, you could easily match

.

Matching hexadecimal color values

Perl Regular Expression Cheat Sheet

Find page title

This handy code snippet will find and print the text within the and tags of a html page.

Parsing Apache logs

Most websites are running on the well-known Apache webserver. If your website does, what about using PHP and some regular expressions to parse Apache logs?

Replacing double quotes by smart qutotes

If you’re a typographer lover, you’ll probably love this regexp, which allow you to replace normal double quotes by smart quotes. A regular expression of that kind is used by WordPress on contents.

Hive Regular Expression Cheat Sheet Printable

Checking password complexity

This regular expression will tests if the input consists of 6 or more letters, digits, underscores and hyphens.
The input must contain at least one upper case letter, one lower case letter and one digit.

WordPress: Using regexp to retrieve images from post

As I know many of you are WordPress users, you’ll probably enjoy that code which allow you to retrieve all images from post content and display it.
To use this code on your blog, simply paste the following code on one of your theme files.

Generating automatic smileys

Hive Regular Expression Cheat Sheet Download

Another function used by WordPress, this one allow you to automatically replace a smiley symbol by an image.

C# Regular Expressions Cheat Sheet

Via 15 PHP regular expressions for web developers