LogoLogo
BlogTwitterDiscordTelegramSignup/Login
  • Getting Started
    • Welcome to Spice.ai Cloud
    • Getting Started
      • Sign in with GitHub
      • Create a Spice app
      • Add a Dataset and query data
      • Add AI Model and chat with your data
      • Next Steps
    • FAQ
  • Features
    • Federated SQL Query
    • Data Acceleration
      • In-Memory Arrow Data Accelerator
      • DuckDB Data Accelerator
      • PostgreSQL Data Accelerator
      • SQLite Data Accelerator
    • Search & Retrieval
    • AI Gateway
    • Semantic Models
    • ML Models
    • Observability
      • Task History
      • Zipkin
  • Building Blocks
    • Data Connectors
      • ABFS
      • ClickHouse
      • Databricks
      • Delta Lake
      • Dremio
      • DuckDB
      • DynamoDB
      • FlightSQL
      • FTP
      • GitHub
      • GraphQL
      • HTTPS
      • LocalPod
      • Memory
      • MSSQL
      • MySQL
      • ODBC
      • Postgres
      • S3
      • SharePoint
      • Snowflake
      • Spark
      • SpiceAI
    • Model Providers
      • Anthropic
      • Azure
      • Hugging Face
      • OpenAI
      • Perplexity
      • SpiceAI
      • XAI
  • API
    • SQL Query API
      • HTTP API
      • Apache Arrow Flight API
    • OpenAI API
    • Health API
  • Portal
    • Playground
      • SQL Query
      • AI Chat
    • Organizations
    • Apps
      • API keys
      • Secrets
      • Connect GitHub
      • Transfer
    • Public Apps
    • App Spicepod
      • Spicepod Configuration
      • Deployments
      • Spice Runtime Versions
    • Monitoring
    • Profile
      • Personal Access Tokens
  • Use-Cases
    • Agentic AI Apps
    • Database CDN
    • Data Lakehouse
    • Enterprise Search
    • Enterprise RAG
  • SDKs
    • Python SDK
      • Streaming
    • Node.js SDK
      • Streaming
      • API Reference
    • Go SDK
    • Rust SDK
    • Dotnet SDK
    • Java SDK
  • Integrations
    • GitHub Copilot
    • Grafana
  • REFERENCE
    • Core Concepts
      • Duration Literals
    • SQL Reference
      • Data Types
      • SQL Functions
        • Aggregate
          • APPROX_COUNT_DISTINCT
          • AVG
          • BIT_AND
          • BIT_OR
          • CORR
          • COUNT
          • COVAR_POP
          • COVAR_SAMP
          • HLL
          • LISTAGG
          • MAX
          • MIN
          • NDV
          • STDDEV
          • STDDEV_POP
          • STDDEV_SAMP
          • SUM
          • VAR_POP
          • VAR_SAMP
        • Binary
          • BASE64
          • BIT_LENGTH
          • FROM_HEX
          • HEX
          • TO_HEX
          • UNBASE64
          • UNHEX
        • Bitwise
          • BIT_AND
          • BIT_OR
          • LSHIFT
          • RSHIFT
          • XOR
        • Boolean
          • IS [NOT] DISTINCT FROM
          • ISFALSE
          • IS [NOT] NULL
          • ISNUMERIC
          • ISTRUE
          • IS_MEMBER
        • Conditional
          • BOOL_AND
          • BOOL_OR
          • CASE
          • COALESCE
          • GREATEST
          • LEAST
          • NULLIF
        • Conversion
          • BINARY_STRING
          • CAST
          • CONVERT_FROM
          • CONVERT_REPLACEUTF8
          • CONVERT_TIMEZONE
          • CONVERT_TO
          • FLATTEN
          • FROM_HEX
          • HASH
          • HEX
          • TOASCII
          • TO_CHAR
          • TO_DATE
          • TO_HEX
          • TO_NUMBER
          • TO_TIME
          • TO_TIMESTAMP
          • UNHEX
        • Cryptography
          • AES_DECRYPT
          • AES_ENCRYPT
          • MD5
          • SHA
          • SHA1
          • SHA256
          • SHA512
        • Data Generation
          • RANDOM
        • Datatype
          • IS_BIGINT
          • IS_DATE
          • IS_INT
          • IS_VARCHAR
          • SIZE
          • TYPEOF
        • Date/Time
          • CONVERT_TIMEZONE
          • CURRENT_DATE
          • CURRENT_DATE_UTC
          • CURRENT_TIME
          • CURRENT_TIMESTAMP
          • DATEDIFF
          • DATE_ADD
          • DATE_DIFF
          • DATE_PART
          • DATE_SUB
          • DATE_TRUNC
          • DAY
          • DAYOFMONTH
          • DAYOFWEEK
          • DAYOFYEAR
          • EXTRACT
          • HOUR
          • LAST_DAY
          • MINUTE
          • MONTH
          • MONTHS_BETWEEN
          • NEXT_DAY
          • QUARTER
          • SECOND
          • TIMESTAMPADD
          • TIMESTAMPDIFF
          • TO_DATE
          • TO_TIME
          • TO_TIMESTAMP
          • UNIX_TIMESTAMP
          • WEEK
          • WEEKOFYEAR
          • YEAR
        • Math
          • ABS
          • ACOS
          • ASIN
          • ATAN
          • CBRT
          • CEILING
          • COS
          • COSH
          • COT
          • DEGREES
          • E
          • EXP
          • FLOOR
          • LOG
          • LOG10
          • MOD
          • PI
          • POWER
          • RADIANS
          • ROUND
          • SIGN
          • SIN
          • SINH
          • SQRT
          • STDDEV
          • STDDEV_POP
          • STDDEV_SAMP
          • TAN
          • TANH
          • TRUNCATE
        • Percentile
          • MEDIAN
          • PERCENTILE_CONT
          • PERCENTILE_DISC
        • Regular Expressions
          • REGEXP_EXTRACT
          • REGEXP_LIKE
          • REGEXP_MATCHES
          • REGEXP_REPLACE
          • REGEXP_SPLIT
        • Semistructured Data
          • ARRAY_CONTAINS
          • MAP_KEYS
          • MAP_VALUES
        • String
          • ASCII
          • BASE64
          • BTRIM
          • CHARACTER_LENGTH
          • CHAR_LENGTH
          • CHR
          • COL_LIKE
          • CONCAT
          • CONCAT_WS
          • ENDS_WITH
          • FROM_HEX
          • HEX
          • ILIKE
          • INITCAP
          • INSTR
          • IS_UTF8
          • LCASE
          • LEFT
          • LENGTH
          • LEVENSHTEIN
          • LIKE
          • LOCATE
          • LOWER
          • LPAD
          • LTRIM
          • MASK
          • MASK_FIRST_N
          • MASK_HASH
          • MASK_LAST_N
          • MASK_SHOW_FIRST_N
          • MASK_SHOW_LAST_N
          • OCTET_LENGTH
          • POSITION
          • QUOTE
          • REGEXP_EXTRACT
          • REGEXP_LIKE
          • REGEXP_MATCHES
          • REGEXP_REPLACE
          • REGEXP_SPLIT
          • REPEAT
          • REPEATSTR
          • REPLACE
          • REVERSE
          • RIGHT
          • RPAD
          • RTRIM
          • SIMILAR_TO
          • SOUNDEX
          • SPLIT_PART
          • STARTS_WITH
          • STRPOS
          • SUBSTRING
          • SUBSTRING_INDEX
          • TOASCII
          • TO_HEX
          • TRANSLATE
          • TRIM
          • UCASE
          • UNBASE64
          • UNHEX
          • UPPER
        • Window
          • COUNT
          • COVAR_POP
          • COVAR_SAMP
          • CUME_DIST
          • DENSE_RANK
          • FIRST_VALUE
          • HLL
          • LAG
          • LEAD
          • MAX
          • MIN
          • NDV
          • NTILE
          • PERCENT_RANK
          • RANK
          • ROW_NUMBER
          • SUM
          • VAR_POP
          • VAR_SAMP
      • SQL Commands
        • SELECT
        • USE
        • SHOW
        • DESCRIBE
        • WITH
    • Release Notes
  • Pricing
    • Paid Plans
    • Community Plan
  • Support
    • Support
  • Security
    • Security at Spice AI
    • Report a vulnerability
  • Legal
    • Privacy Policy
    • Website Terms of Use
    • Terms of Service
    • End User License Agreement
Powered by GitBook
On this page

Was this helpful?

Edit on GitHub
Export as PDF
  1. REFERENCE
  2. SQL Reference
  3. SQL Functions

String

Function Name
Description

Returns the ASCII code for the first character of a string. If the string is empty, 0 is returned.

Returns the Base64 encoding of a binary string.

Trims leading and trailing characters from a string.

Returns the length of an input string.

Returns the character length of the input string.

Converts a Unicode code point into the character that matches the input Unicode character. If an invalid code point is specified, an empty string is returned.

Tests whether an expression column matches a pattern column. Comparisons are case-sensitive.

Concatenates two or more strings. NULL values are ignored.

Concatenate with separator. Returns a string resulting from the joining of two or more string values in an end-to-end manner. Uses the first argument as the separator between each string.

Returns whether a string ends with another string. The comparison is case-sensitive.

Returns a binary value for the given hexadecimal string.

Returns the hexadecimal encoding of an expression.

Tests whether an expression matches a pattern. The comparison is case-insensitive.

Returns the input string with the first letter of each word in uppercase and the subsequent letters in the word are in lowercase).

Returns the position of the first occurrence of a string when it is contained in another string. If no such occurrence is found, a zero is returned. The comparison is case-sensitive.

Returns whether an expression is valid UTF-8.

Returns the input expression with all the characters converted to lowercase.

Returns the left-most substring. The function name must be enclosed in double quotes ("LEFT").

Returns the length of an input string. If the character encoding isn’t specified, it assumes to UTF8.

Computes the Levenshtein distance between two input expressions.

Tests whether an expression matches one or more patterns. Comparisons are case-sensitive.

Searches for the first occurrence of the first argument in the second argument and if found, returns the position the of the first argument in the second argument. The first character in a string is position 1. Returns 0 if the substring isn’t found in the expression.

Returns the input expression with all the characters converted to lowercase.

Left pads a string with spaces or specified characters to reach the number of characters specified as a parameter.

Removes leading spaces or characters from a string.

Returns a masked version of a string.

Returns a masked version of a string with the first num_chars characters masked. By default, if you do not provide a mask value, the first four characters are masked.

Returns a consistent hash value based on the input string. This function returns NULL for non-string types.

Returns a masked version of a string with the last num_chars characters masked. By default, if you do not provide a mask value, the last four characters are masked.

Returns a masked version of a string with the first num_chars characters unmasked. By default, if you do not provide a value, the first four characters are shown.

Returns a masked version of a string with the last num_chars characters unmasked. By default, if you do not provide a value, the last four characters are shown.

Returns the length of the string in bytes.

Returns the position of the first occurrence of a substring within another string.

Returns a result that can be used as a properly escaped data value in a SQL statement.

Extracts the first string in expression that matches the REGEXP expression and corresponds to the REGEX group index.

Returns true when the specified regular expression matches values in a column. Otherwise, returns false.

Returns true when the specified regular expression matches values in a column. Otherwise, returns false.

Finds strings that match the given regular expression and replaces the strings with the given string.

Splits an input string by using a regular expression according to a keyword and an integer value.

Builds a string by repeating the input for the specified number of times.

Repeats the given string n times.

Removes all occurrences of a specified substring and replaces them with another string.

Reverses the order of characters in a string.

Returns the right-most substring. The function name must be enclosed in double quotes (“RIGHT”).

Right pads a string with spaces or specified characters to reach the number of characters specified as a parameter.

Removes trailing spaces or characters from a string.

Tests whether the entire expression matches a pattern.

Returns a string that contains a phonetic representation of the input string.

Splits a given string at a specified character and returns the requested part.

Returns whether a string starts with another string. The comparison is case-sensitive.

Searches for the first occurrence of the substring in the given expression and returns the position of where the substring begins. Searching binary values is also supported.

Returns the portion of the string from the specified base expression starting at the specified characters.

Returns a substring of an expression before the specified number of delimiters occurs.

Converts a string that is encoded in the specified character set to UTF-8.

Returns a hexadecimal string for the given binary value.

Translates the base expression from the source characters/expression to the target characters/expression.

Removes leading, trailing, or both spaces or characters from a string.

Returns the input expression with all the characters converted to uppercase.

Decodes a Base64-encoded string.

Converts the hexadecimal number into the bytes represented by a number.

Returns the input expression with all the characters converted to uppercase.

Last updated 2 years ago

Was this helpful?

ASCII
BASE64
BTRIM
CHARACTER_LENGTH
CHAR_LENGTH
CHR
COL_LIKE
CONCAT
CONCAT_WS
ENDS_WITH
FROM_HEX
HEX
ILIKE
INITCAP
INSTR
IS_UTF8
LCASE
LEFT
LENGTH
LEVENSHTEIN
LIKE
LOCATE
LOWER
LPAD
LTRIM
MASK
MASK_FIRST_N
MASK_HASH
MASK_LAST_N
MASK_SHOW_FIRST_N
MASK_SHOW_LAST_N
OCTET_LENGTH
POSITION
QUOTE
REGEXP_EXTRACT
REGEXP_LIKE
REGEXP_MATCHES
REGEXP_REPLACE
REGEXP_SPLIT
REPEAT
REPEATSTR
REPLACE
REVERSE
RIGHT
RPAD
RTRIM
SIMILAR_TO
SOUNDEX
SPLIT_PART
STARTS_WITH
STRPOS
SUBSTRING
SUBSTRING_INDEX
TOASCII
TO_HEX
TRANSLATE
TRIM
UCASE
UNBASE64
UNHEX
UPPER