---
schema_version: "1"
id: 6f39a157494207847002db5d1a2b3b80c1969d740737f9ccfc85e613379319b8
title: String Functions and Operators
collection: developer
version: current
locale: en-us
portal_url: https://docs.salesforce.com/developer/current/en-us/data-cloud/data-cloud-query-guide/references/data-cloud-sql-reference/scalar-func/string-func/string-func.md
canonical_url: https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/string-func.html
source_site: developer.salesforce.com
content_type: documentation
content_hash: eaf460ce5dba4654cda870e4fb8b0de055a2e0ad02ad30ff009e5e56136b6f7c
products:
  - Data 360 Query Guide
  - Data 360 Developer Documentation
---
# String Functions and Operators

**Applies to:** ✅ Data 360 SQL ✅ Tableau Hyper API

Functions and operators for examining and manipulating string values. Use string functions to:

- **Transform case** by converting strings to uppercase, lowercase, or title case.
- **Extract and search** substrings, find positions, and split text.
- **Pad and trim** strings to adjust length and remove unwanted characters.
- **Encode and hash** data for storage or security purposes.

## Considerations

- **Character and byte length**: Functions like `char_length` count characters, but `octet_length` counts bytes. This distinction matters for multi-byte UTF-8 characters.
- **1-based indexing**: String positions start at 1, not 0.
- **NULL handling**: The `||` operator returns NULL if either operand is NULL. Use `concat` to ignore NULL values.

For more information about supported types, see [String Types](https://developer.salesforce.com/docs/data/data-cloud-query-guide/datatype/string.md).

## Concatenation

Combine strings and values:

- [String Concatenation Operator (||)](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/concatenation-operator.md) — Concatenate two strings or a string a non-string value (NULL-sensitive).
- [CONCAT](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/concat.md) — Concatenate multiple values, ignoring NULLs.

## Length and Measurement

Measure string size:

- [BIT_LENGTH](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/bit-length.md) — Get the number of bits in a string.
- [CHAR_LENGTH / CHARACTER_LENGTH](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/char-length.md) — Count the number of characters in a string.
- [LENGTH](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/length.md) — Count the number of characters in a string.
- [OCTET_LENGTH](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/octet-length.md) — Get the number of bytes in a string.

## Case Conversion

Transform string case:

- [LOWER](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/lower.md) — Convert to lowercase.
- [UPPER](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/upper.md) — Convert to uppercase.
- [INITCAP](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/initcap.md) — Capitalize the first letter of each word.

## Substring Operations

Extract and locate substrings:

- [SUBSTRING](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/substring.md) — Extract a substring by position and length.
- [SUBSTR](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/substr.md) — Extract a substring (alternative syntax).
- [LEFT](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/left.md) — Get the first _n_ characters.
- [RIGHT](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/right.md) — Get the last _n_ characters.
- [POSITION](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/position.md) — Find the location of a substring.
- [STRPOS](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/strpos.md) — Find substring location (alternative syntax).
- [OVERLAY](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/overlay.md) — Replace a substring at a position.
- [SPLIT](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/split.md) — Split a string into an array by delimiter.
- [SPLIT_PART](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/split-part.md) — Split a string on delimiter and return a field.

## Trimming and Padding

Adjust string length:

- [TRIM](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/trim.md) — Remove characters from start, end, or both.
- [BTRIM](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/btrim.md) — Remove characters from both ends.
- [LTRIM](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/ltrim.md) — Remove characters from the start.
- [RTRIM](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/rtrim.md) — Remove characters from the end.
- [LPAD](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/lpad.md) — Pad a string on the left.
- [RPAD](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/rpad.md) — Pad a string on the right.
- [SPACE](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/space.md) — Generate a string of spaces.

## String Modification

Transform and manipulate strings:

- [REPLACE](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/replace.md) — Replace all occurrences of a substring.
- [REPEAT](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/repeat.md) — Repeat a string multiple times.
- [REVERSE](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/reverse.md) — Reverse the characters in a string.

## Search and Match

Check for substrings and string boundaries:

- [CONTAINS / ICONTAINS](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/contains.md) — Check whether a string contains a substring.
- [STARTS_WITH](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/starts-with.md) — Check whether a string starts with a prefix.
- [ENDS_WITH](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/ends-with.md) — Check whether a string ends with a suffix.

## Character Conversion

Convert between characters and codes:

- [ASCII](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/ascii.md) — Get the ASCII code of the first character.
- [CHR](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/chr.md) — Get the character for an ASCII code.

## Encoding and Hashing

Encode data and compute hashes:

- [ENCODE](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/encode.md) — Encode binary data to text (base64, hex, escape).
- [DECODE](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/decode.md) — Decode text to binary data.
- [TO_BASE64](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/to-base64.md) — Convert data to base64.
- [TO_HEX](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/to-hex.md) — Convert data to hexadecimal.
- [MD5](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/md5.md) — Compute the MD5 hash for data.
- [SHA256](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/sha256.md) — Compute the SHA-256 hash for data.

## Quoting

Prepare strings for SQL statements:

- [QUOTE_IDENT](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/quote-ident.md) — Quote a string as an SQL identifier.
- [QUOTE_LITERAL](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/quote-literal.md) — Quote a string as an SQL literal.

## Examples

### Data Cleaning

Normalize and clean user input.

```sql
SELECT
    trim(both ' ' from input_text) AS trimmed,
    lower(trim(both ' ' from input_text)) AS normalized
FROM user_input;
```

### Text Parsing

Extract components from structured strings.

```sql
SELECT
    split_part(email, '@', 1) AS username,
    split_part(email, '@', 2) AS domain
FROM users;
```

### String Formatting

Format data for display.

```sql
SELECT
    initcap(first_name) || ' ' || initcap(last_name) AS full_name,
    lpad(employee_id::text, 6, '0') AS padded_id
FROM employees;
```

### Search and Replace

Find and modify text patterns.

```sql
SELECT
    replace(description, 'old_term', 'new_term') AS updated_description
FROM products
WHERE position('old_term' in description) > 0;
```

## Related Documentation

- [String Types](https://developer.salesforce.com/docs/data/data-cloud-query-guide/datatype/string.md)
- [String Pattern Matching](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/string-matching/string-matching.md)
- [Data Type Formatting Functions](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/formatting-func/formatting-func.md)