Skip to content

Product Documentation

Main NavigationUser Guide Yellowbrick

Appearance

Sidebar Navigation

Architecture & Concepts

Bulk Data Loading

Compute Clusters

Databases

FIPS 140-2

Licensing

Reserved Nodes

Sample Data

Shared Services

Backup & Restore

Overview

Backup and Restore Clients

Backup and Restore Glossary

Backup Strategies

Restore Operations

ybbackup Commands

ybbackup Examples

ybbackup Options

ybbackupctl Commands

ybbackupctl Examples

ybbackupctl Options

ybrestore Commands

Restoring Users and Roles

Running a Single-Table Restore

Running Incremental Restores

ybrestore Examples

ybrestore Options

Decrypting Columns in a Restored Database

HOT_STANDBY and READONLY Modes

Monitoring Backup and Restore with SMC

Client Tools & Drivers

Installing ybtools

Installing the "generic" ybtools

Installing ybtools on AIX

Installing ybtools on Linux

Installing ybtools on macOS

Installing ybtools on Windows

Setting Up a Database Connection

Configuring SSL/TLS for Tools and Drivers

Secure Connections for ODBC/JDBC Clients and ybsql

Secure Connections for DBeaver Clients

Secure Connections for JDBC Clients

Secure Connections for ODBC Clients

Secure Connections for ybsql (libpq)

Obtaining a Certificate Chain

Secure Connections for Java-based ybtools

SSL/TLS Overview

Troubleshooting SSL Issues

Verifying SSL/TLS Encryption

Common Environment Variables in ybtools

Common Options in ybtools

Examples of OAuth 2.0/OIDC Connections

Object Storage Options

Downloading Drivers

Installing the Java Runtime Environment

Opening Network Ports for Clients

Uninstalling ybtools

Using the ADO.NET Driver

Configuration Parameters

Compatibility Parameters

enable_alternative_round

enable_safe_compat_functions_with_nz

override_version

Data Processing and Formatting

datestyle

enable_silent_coerce

enable_subquery_remove_useless_order_by

extra_float_digits

json_missing_element_default

max_levenshtein_distance

max_levenshtein_string_size

timezone

Feature Enablement

bytea_output

enable_full_bytea

enable_full_funcscan

enable_full_json

enable_full_lateral_join

enable_password_policy

enable_user_audit

ybd_allow_udf_creation

General

application_name

client_encoding

client_min_messages

cluster_name

enable_rowpacket_compression_in_distribution

enable_user_audit_extended

idle_in_transaction_session_timeout

idle_session_timeout

listen_addresses

log_timezone

max_connections

max_quota_objects

max_user_connections

override_version

port

search_path

statement_timeout

tcp_keepalives_count

tcp_keepalives_idle

tcp_keepalives_interval

user_audit_max_bytes_per_file

user_audit_max_files

yb_last_execid

yb_server_version

yb_server_version_num

ybd_query_tags

Tuning

enable_query_trace

enable_se_filter_expression_generation

se_tablescan_lookahead_kb

ybd_analyze_after_loads

ybd_analyze_after_writes

Non-functional

Database Administration

Encrypting Sensitive Data

Deprecated Encryption Functions

Encryption Key Rotation

SQL Encryption Examples

Managing Tables and Views

Auto-Analyzing Tables

Disk Quotas

Distributing Data

External Tables

Generating Values with Sequences

Partitioning Tables

System Columns in Tables

System Views

sys.lock

Types of Locks

Retention Policies for System Relations

sys.alert

sys.backup

sys.backup_chain

sys.backup_snapshot

sys.blade

sys.cluster

sys.cluster_status

sys.column

sys.const

sys.database

sys.drive_summary

sys.external_authentication

sys.external_format

sys.external_location

sys.external_storage

sys.hardware_instance_assignment

sys.hardware_instance_limits

sys.hardware_instance_type

sys.hardware_instance_usage

sys.instance_id

sys.key

sys.load

sys.log_alert

sys.log_analyze

sys.log_audit

sys.log_authentication

sys.log_backup

sys.log_cluster_event

sys.log_error

sys.log_load

sys.log_query

sys.log_query_alert

sys.log_query_analyze

sys.log_query_explain

sys.log_replica_status

sys.log_restore

sys.log_retention

sys.log_session

sys.log_unload

sys.log_warning

sys.mount

sys.password_policy

sys.procedure

sys.query

sys.query_analyze

sys.query_explain

sys.query_rule_event

sys.remote_server

sys.replica

sys.replica_status

sys.restore

sys.retention

sys.role

sys.schema

sys.sequence

sys.session

sys.storage

sys.table

sys.table_flush_summary

sys.table_info

sys.table_partition

sys.table_storage

sys.unload

sys.user

sys.view

sys.wlm_active_pool

sys.wlm_active_profile

sys.wlm_active_rule

sys.wlm_pending_pool

sys.wlm_pending_rule

sys.worker

Canceling Queries

Creating Databases

Creating Stored Procedures

Cross-Database Queries

Database Limits

Database Objects

Generating DDL

Managing Database Users and Roles

Managing Idle Sessions

Managing the Row Store

Password Policies

Running Queries

User Audit Log

Database Replication

Managing Replication

Dropping a Replica

Pausing and Resuming Replication

Replication Failover and Failback

Rolling Back a Replicated Database

Setting Up Replication

Configuring SSL Trust

Monitoring Replication

Seeding a Replica

Setting Up Loopback Replication

Overview

Replication Glossary

Replication SQL Commands

Loading Tables

Bulk Loading Tables

Bulk Load Examples

Bulk Load Insert Examples

Delete, Update, and Upsert Examples

Examples with Duplicate Rows

Running a Bulk Load

Loading Tables from Parquet Files

Parquet Date and Time Conversion

Parquet Load Examples

Parquet Schema Mapping and Type Casting

Analyzing Rejected Rows

Deletes, Updates, and Upserts

Handling Duplicate Rows

Loading Data Exported from SQL Server

Loading Generated Key Values

Monitoring Load Operations

Resuming a Partial Load

Saving Load Options to a File

Setting the Commit Interval

Specifying NULL Behavior for Different Columns

Using the Dry Run Option

ybload Command

Character Escape Sequences

Escaping Quotes in Windows Clients

NULL and Empty Markers

Setting --format for Flat Files

ybload Advanced Processing Options

ybload Date Formats

ybload Field Options

ybload Options

ybload Time Formats

ybload Timestamp Formats

Loading Data from Object Storage

Loading from Amazon S3

S3 Authentication Methods

S3 Object Storage Examples

S3 Object Storage URIs

Loading from Azure Blob Storage

Azure Authentication Methods

Azure Blob Storage Examples

Azure Blob Storage URIs

Loading from S3-Compatible Object Storage

Loading Tables with Spark

Setting up and Running a Spark Job

Error Tolerance

Examples of Spark Jobs

Output for a Spark Job

Spark Application Options

Spark Format for Reading Schemas

Setting Up the ybrelay Service

Creating a Java Keystore

Running the ybrelay-init Script

Starting the ybrelay Service Manually

TLS Support for ybrelay and Spark

Spark and ybrelay Glossary

ybrelay Options

Trickle Loading Data via JDBC

Example of a Trickle Load Java Program

Performance Factors

Performance Tuning

Metering

Automatic Uploads and Downloads

dim.application

dim.client

dim.cluster

dim.date

dim.hardware_instance_type

dim.query

dim.user

fact.instance_metering

fact.top_query

Metering Use Cases

metering.vcpu_consumption

metering.vcpu_estimate_per_query

Platform Support Guide

Appliance: LDAP Integration

LDAP Authentication

LDAP Authentication Modes

LDAP Authentication Settings

LDAP Bind Examples

Search, then Bind LDAP Example

Setting Up LDAP Authentication

Synchronizing Users and Groups

LDAP Synchronization Settings

LDAP Synchronization: Basic Example

LDAP Synchronization: Examples with Filters

Importing LDAP Certificates

LDAP Sample Schema

Appliance: System Administration

Appliance: Disk Encryption

Setting Up Encrypted Drives

Example: Force Keystore Setup

Example: Set Up the Keystore and Enable Encryption

Example: Use Multiple Unlock Keys

Example: Rotate the Keystore with Multiple Unlock Keys

Managing Encrypted Drives

Remote Diagnostics

Configuring Remote Diagnostics

Diagnostics Service Levels

Enabling Remote Diagnostics

System Alerts

Creating an Alert Endpoint

Alert Templates

Endpoint Types

Alert Types

Managing Alerts

Using the System Management Console

Configuration Settings in the SMC

Introduction to the SMC

SMC Console Login

ybcli Reference

ybcli: config

ybcli: config bmc

ybcli: config cert

ybcli: config dba

ybcli: config firewall

ybcli: config hostname

ybcli: config keystore

ybcli: config network

ybcli: config ntp

ybcli: config passwords

ybcli: config phonehome

ybcli: config timezone

ybcli: config user

ybcli: blade

ybcli: clear

ybcli: database

ybcli: encryption

ybcli: exit, quit

ybcli: health

ybcli: help

ybcli: keystore

ybcli: log

ybcli: manager

ybcli: status

ybcli: system

Chassis Management

Checking the Logs

Manager Node Failover

Manager Node Ports

Powering the Appliance Off and On

Starting and Stopping the Database

System Maintenance

User Accounts

Using Network Tracing

Cloud: Configuration

Vanity DNS

Configuring DNS Records for AWS

Configuring DNS Records for Azure

Configuring DNS Records for GCP

Yellowbrick Manager

Yellowbrick Manager Infrastructure Administrator

Yellowbrick Manager Instance Login

Yellowbrick Manager on AWS

Yellowbrick Manager on Azure

Yellowbrick Manager on GCP

Alerting

Configuring SSL Certificates

Custom Object Storage

Custom Security Agents

Multi-Instance Configuration

Single Sign-On

Cloud: Getting Started

SQL-Based Loads from External Storage

LOAD TABLE Steps

Loading a Table via the Load Assistant

Suspending and Resuming Instances

Users and Roles

Walkthrough tour

Cloud: Installation

CLI Install Instructions

Deployer CLI Reference

Timeouts

Permissions

AWS Installation Permissions

Azure Installation Permissions

GCP Installation Permissions

Private Install Instructions

Azure Reference Terraform

Azure Terraform - ACR

Azure Terraform - AKS

Azure Terraform - DNS

Azure Terraform - Firewall

Azure Terraform - Network

Azure Terraform - Provider

AWS Private Instructions

Azure Private Instructions

GCP Private Instructions

Public Install Instructions

AWS Public Instructions

Azure Public Instructions

GCP Public Instructions

Access Key

Deployer Web UI Notes

Resource Tagging and Costing

Cloud: Kubernetes Guides

Kubeconfig Setup on AKS

Kubeconfig Setup on EKS

Kubeconfig Setup on GKE

Kubectl Administration

Yellowbrick Operator

Cloud: Supported Regions

SQL Reference

SQL Commands

CREATE EXTERNAL FORMAT

External Format Load Options

CREATE EXTERNAL TABLE

External Table Examples

EXTERNAL TABLE USING Options

CREATE TABLE

CREATE TABLE Examples

Distribution Options

Partitioning Options

Sorted and Clustered Tables

GRANT

Abbreviations for ACLs

ON CLUSTER

ON DATABASE

ON EXTERNAL object

ON KEY

ON PROCEDURE

ON ROLE

ON SCHEMA

ON SEQUENCE

ON SYSTEM

ON TABLE

ROLE

SELECT

GROUP BY Clause

GROUP BY CUBE

GROUP BY GROUPING SETS

GROUP BY ROLLUP

Subqueries

Correlated Subqueries

Subquery Examples

FROM Clause

HAVING Clause

LIMIT Clause

OFFSET Clause

ORDER BY Clause

SELECT List

SETTING Clause

UNION, INTERSECT, EXCEPT

WHERE Clause

WINDOW Clause

WITH Clause

ABORT

ALTER CLUSTER

ALTER DATABASE

ALTER DATABASE ADD REPLICA

ALTER DATABASE ALTER REPLICA

ALTER DATABASE DROP REPLICA

ALTER DEFAULT PRIVILEGES

ALTER EXTERNAL AUTHENTICATION

ALTER EXTERNAL FORMAT

ALTER EXTERNAL LOCATION

ALTER EXTERNAL STORAGE

ALTER PROCEDURE

ALTER REMOTE SERVER

ALTER ROLE

ALTER SCHEMA

ALTER SEQUENCE

ALTER SYSTEM

ALTER SYSTEM SET CLUSTER

ALTER TABLE

ALTER USER

ALTER USER SET DEFAULT_CLUSTER

ALTER VIEW

ALTER WLM PROFILE

ALTER WLM RESOURCE POOL

ALTER WLM RULE

BEGIN

CALL

CANCEL

CLOSE

COMMENT ON

COMMIT

COPY

CREATE CLUSTER

CREATE DATABASE

CREATE EXTERNAL AUTHENTICATION

CREATE EXTERNAL LOCATION

CREATE EXTERNAL MOUNT

CREATE EXTERNAL STORAGE

CREATE KEY

CREATE PROCEDURE

CREATE REMOTE SERVER

CREATE ROLE

CREATE SCHEMA

CREATE SEQUENCE

CREATE TABLE AS

CREATE USER

CREATE VIEW

CREATE WLM PROFILE

CREATE WLM RESOURCE POOL

CREATE WLM RULE

DEALLOCATE

DECLARE

DELETE

DESCRIBE

DROP BACKUP CHAIN

DROP CLUSTER

DROP DATABASE

DROP EXTERNAL AUTHENTICATION

DROP EXTERNAL FORMAT

DROP EXTERNAL LOCATION

DROP EXTERNAL MOUNT

DROP EXTERNAL STORAGE

DROP KEY

DROP OWNED BY

DROP PROCEDURE

DROP REMOTE SERVER

DROP ROLE

DROP SCHEMA

DROP SEQUENCE

DROP TABLE

DROP USER

DROP VIEW

DROP WLM PROFILE

DROP WLM RESOURCE POOL

DROP WLM RULE

END

EXECUTE

EXPLAIN

FETCH

HELP

IMPORT SSL TRUST

INSERT

LIST BUCKETS

LIST OBJECTS

LOAD TABLE

LOCK SEQUENCE

MOVE cursor

MOVE query

PREPARE

REASSIGN OWNED BY

RELEASE SAVEPOINT

RESET

RESTART query

REVOKE

REVOKE SSL TRUST

ROLLBACK

ROLLBACK DATABASE TO SNAPSHOT

ROLLBACK TO SAVEPOINT

SAVEPOINT

SELECT INTO

SET

SET ROLE

SET SESSION AUTHORIZATION

SHOW

SHOW SSL CA

SHOW SSL IDENTITY

SHOW SSL SYSTEM

SHOW SSL TRUST

SQL Identifiers

START TRANSACTION

SUSPEND INSTANCE

TABLE

TRUNCATE

UPDATE

USE CLUSTER

SQL Data Types

Data Type Casting

Explicit Casting

Implicit Casting

Implicit Casting Examples

DECIMAL

Calculations with DECIMAL Values

Examples of DECIMAL Calculations

SQL String Constants

C-style Escape Sequences in String Constants

Escape Sequences for Non-Printable Characters

Unicode Escape Sequences in String Constants

BIGINT

BINARY

BOOLEAN

CHAR

DATE

DOUBLE PRECISION

INTEGER

IPV4

IPV6

JSON

JSONB

MACADDR

MACADDR8

REAL

SMALLINT

TIME

TIMESTAMP

TIMESTAMP WITH TIME ZONE

Trailing Blanks in Character Data

UUID

VARCHAR

SQL Functions and Operators

Aggregate Functions

AVG

COUNT

COUNT (DISTINCT)

GROUP_CONCAT

GROUPING

LISTAGG

MAX

MEDIAN()

MIN

PERCENTILE_CONT

PERCENTILE_DISC

STDDEV_SAMP and STDDEV_POP

STRING_AGG

SUM

VAR_SAMP and VAR_POP

Conditional Expressions

CASE

COALESCE

DECODE

GREATEST and LEAST

IIF

ISNULL

NULLIF

NVL

NVL2

Datetime Functions

ADD_MONTHS

AGE

AT TIME ZONE

CURRENT_DATE

CURRENT_TIMESTAMP

CURRENT_UTC_TIMESTAMP()

DATE_PART

DATE_TRUNC

DATEADD

DATEDIFF

DATENAME

DAY

DAYS_BETWEEN

EOMONTH

EXTRACT

GETDATE

JUSTIFY_DAYS, _HOURS, _INTERVAL

LAST_DAY

LOCALTIME

LOCALTIMESTAMP

MAKE_DATE

MAKE_INTERVAL

MAKE_TIME

MAKE_TIMESTAMP

MAKE_TIMESTAMPTZ

MONTH

MONTHS_BETWEEN

NEXT_DAY

NOW()

OVERLAPS

Supported Date Parts

TIMEOFDAY()

TIMEZONE

YEAR

YEARS_BETWEEN

Formatting Functions

FLOAT4, FLOAT8

Formats for Datetime Values

Formats for Numeric Values

INT2, INT4, INT8

TO_CHAR

TO_DATE

TO_NUMBER

TO_TIMESTAMP

JSON Functions

CHECK_JSON

FLATTEN

JSON_ARRAY_STR

JSON_EXTRACT

JSON_INGEST

JSON_LOOKUP

JSON_OBJECT_STR

JSON_TYPEOF

Querying JSON Data

Mathematical Functions

ABS

ACOS

ASIN

CBRT

CEIL

COS

DEGREES

DIV

ERF

EXP

FLOOR

INV_NORM

LN

LOG

Mathematical Operators

MOD

POWER

PROBNORM

RADIANS

RANDOM()

ROUND

ROUND_VAR

SIGN

SIN

SQRT

TRUNC

Network Address Functions

CONTAINS

HOSTMASK

INET_MERGE

IPV4_GET_INET, IPV6_GET_INET

IPV4_GET_MASKBITS, IPV6_GET_MASKBITS

IPV4_NETMASK, IPV6_NETMASK

MACADDR8_SET7BIT

TEXT

TRUNC

Pattern Matching

Regular Expression Details

Alternation Constructs

Anchors

Back References

Character Classes and Escaped Special Characters

Flags

Grouping Constructs

Quantifiers

SQL Operators and Pattern Matching Functions

LIKE

REGEXP_EXTRACT

REGEXP_INSTR

REGEXP_LIKE

REGEXP_REPLACE

SIMILAR TO

SIMILAR TO: Best Practices

Simple Regex Examples

SQL Regex Operators

SUBSTRING (Pattern Match)

SQL Conditions

ANY

BETWEEN

Comparison Operators

EXISTS

IN

Logical Operators

SQL User Defined Function (UDF)

SQL UDF Create Function

GRANT EXECUTE statement

SQL UDF Examples

SQL UDF Function Overloading

Troubleshooting: Common Errors and Solutions

String Functions

ENCRYPT_KS

ENCRYPT_KS Examples

Encryption and Decryption Algorithms

ASCII

BIT_LENGTH

BTRIM

CHAR_LENGTH

CHR

CONCAT_WS

CONCAT, ||

DECRYPT (deprecated)

DECRYPT_KS

DLE_DST

ENCODE

ENCRYPT (deprecated)

HASH

HASH4

HASH8

HMAC

HMAC_KS

INITCAP

INSTR

LE_DST

LEFT

LENGTH

LOWER

LPAD

LTRIM, RTRIM

MD5

NYSIIS

OCTET_LENGTH

POSITION

REPEAT

REPLACE

REVERSE

RIGHT

RPAD

SPLIT_PART

STRPOS

SUBSTR

SUBSTRING

TO_ASCII

TO_HEX

TRANSLATE

TRIM

UPPER

System Functions

CURRENT_CLUSTER()

CURRENT_DATABASE()

CURRENT_QUERY()

CURRENT_SCHEMA

CURRENT_SCHEMAS()

CURRENT_SETTING

CURRENT_USER

DEFAULT_CLUSTER()

GETDATABASEENCODING()

HAS_CLUSTER_PRIVILEGE

HAS_DATABASE_PRIVILEGE

HAS_EXTERNAL_*_PRIVILEGE

HAS_FUNCTION_PRIVILEGE

HAS_KEY_PRIVILEGE

HAS_ROLE_PRIVILEGE

HAS_SCHEMA_PRIVILEGE

HAS_SYSTEM_PRIVILEGE

HAS_TABLE_PRIVILEGE

PG_ADVISORY_LOCK

PG_ADVISORY_UNLOCK

PG_HAS_ROLE

PG_RELOAD_CONF()

PG_TRY_ADVISORY_LOCK

SESSION_USER

sys.execution_id()

sys.filesys_number_from_rowid

sys.gen_random_uuid()

sys.inode_number_from_rowid

sys.ldap_sync

sys.oldest_rollback_point_id_in_replica

sys.row_number_from_rowid

sys.worker_id_from_rowid

sys.worker_id()

sys.worker_uuid

SYSTEM_CLUSTER()

VERSION()

yb_get_view_table_usage()

yb_terminate_session

Type-Safe Casting Functions

SAFE_TO_DATE

SAFE_TO_DOUBLE

SAFE_TO_TIMESTAMP

Window Functions

AVG (window function)

COUNT (window function)

CUME_DIST

DENSE_RANK

FIRST_VALUE, LAST_VALUE

LEAD, LAG

MAX (window function)

MEDIAN (window function)

MIN (window function)

NTILE

PERCENT_RANK

PERCENTILE_CONT (window function)

PERCENTILE_DISC (window function)

RANK

ROW_NUMBER

STDDEV_SAMP and STDDEV_POP (window functions)

SUM (window function)

Syntax for Window Functions

VAR_SAMP and VAR_POP (window functions)

CONVERT (SQL Server Migration Function)

GETBIT Function

NEXTVAL Function

Supported Functions and Return Types

SQL Reserved Words

Unloading Data

Unloading Data to Object Storage

Unloading Data to an S3 Bucket

Unloading Data to Azure Storage

Unloading Data to Parquet Files

Data Type Mapping for Parquet Unloads

Parquet Processing Options

ybunload Command

Saving Unload Options to a File

ybunload Examples

ybunload Options

ybunload Output Files

Setting Up an Unload

Workload Management

How WLM Works

Creating WLM Resource Pools

Allocating Query Memory

Examples of Flex Pools

Creating WLM Rules

Rule Examples

Assemble Rule Examples

Compile Rule Examples

Completion Rule Examples

Examples of JavaScript for Rules

Restart Rule Examples

Runtime Rule Examples

Submit Rule Examples

JavaScript Properties for WLM Rules

Recoverable Error Codes

Rule Actions

Rule Conditions

Rule Processing

Rule Types

A Simple WLM Example

Creating WLM Profiles

Moving Queries

Restarting Queries

Throttling Concurrency

Using Query Tags

How Queries Are Executed

Sharing System Resources

ybsql Reference

ybsql \copy Command

\copy Examples

\copy Parameters

ybsql Properties and Variables

Examples with ybsql Variables

ybsql Display Properties

ybsql Prompt Variables

ybsql Properties

ybsql User Variables

ybsql Command-Line Options

ybsql Connections

ybsql Environment Variables

ybsql Examples

ybsql Slash (\) Commands

ybsql Startup File

Yellowbrick Release Notes

Detailed change log

Error Codes

Glossary

Third-Party Licenses

Vector Store

On this page

Table of Contents for current page

Yellowbrick Documentation
|
SQL Reference
|
SQL Functions and Operators
|
Pattern Matching

All platforms

Pattern Matching

This section covers pattern matching operators and functions, including supported regular expression (regex) operators and syntax.

Topics in this section:

Regular Expression Details
SQL Operators and Pattern Matching Functions

Parent topic: SQL Functions and Operators

Previous pageTRUNC

Next pageRegular Expression Details

© 2018 - 2024 Yellowbrick Data, Inc. All Rights Reserved. | Terms and Privacy Policy