> ## Documentation Index
> Fetch the complete documentation index at: https://docs.narrative.io/llms.txt
> Use this file to discover all available pages before exploring further.

# Narrative Functions

> Platform-specific functions: DELTA, budget functions, and more

## Narrative-specific functions

These functions are specific to the Narrative platform.

### NORMALIZE\_EMAIL

Normalizes an email address for consistent matching.

```sql theme={null}
SELECT NORMALIZE_EMAIL('John.Doe+test@Gmail.COM') -- 'johndoe@gmail.com'
SELECT NORMALIZE_EMAIL(email) AS normalized_email FROM company_data."123"
```

Normalization includes:

* Lowercase conversion
* Removal of plus-addressing
* Provider-specific handling (Gmail dot removal, etc.)

### NORMALIZE\_PHONE

Normalizes a phone number to a standard format.

```sql theme={null}
SELECT NORMALIZE_PHONE('NATIONAL', '+15145555555', null) AS normalized
SELECT NORMALIZE_PHONE('E164', phone, 'US') AS e164_phone FROM company_data."123"
```

### HASH

Generates a hash from one or more values.

```sql theme={null}
SELECT HASH('value1', 'value2') AS combined_hash
SELECT HASH(user_id, email) AS user_hash FROM company_data."123"
```

### MD5 / SHA1 / SHA2

Standard cryptographic hash functions.

```sql theme={null}
SELECT MD5('hello')           -- MD5 hash
SELECT SHA1('hello')          -- SHA-1 hash
SELECT SHA2('hello', 256)     -- SHA-256 hash
```

### HMAC\_SHA256

HMAC using SHA-256.

```sql theme={null}
SELECT HMAC_SHA256('message', 'secret_key') AS hmac
```

### ADDRESS\_HASHES

Generates hash variants for address matching.

```sql theme={null}
SELECT ADDRESS_HASHES('123 Main St', 'New York', 'NY', '10001') AS hashes
```

### NARRATIVE\_ID\_ENCODE

Encodes a clear text identifier into a Narrative ID within a specific encoding space. Narrative IDs are per-partner pseudonymous identifiers that enable privacy-safe data collaboration.

**Syntax:**

```sql theme={null}
NARRATIVE_ID_ENCODE(raw_identifier, key_id)
```

**Parameters:**

| Parameter        | Type   | Description                                                            |
| ---------------- | ------ | ---------------------------------------------------------------------- |
| `raw_identifier` | string | The clear text identifier to encode (email, hashed email, phone, etc.) |
| `key_id`         | string | The encoding key ID specifying the target encoding space               |

**Returns:** string - The encoded Narrative ID

**Examples:**

```sql theme={null}
-- Encode an email address
SELECT NARRATIVE_ID_ENCODE(LOWER(TRIM(email)), 'your_org_key_id') AS narrative_id
FROM company_data."123"

-- Encode an already-hashed email
SELECT NARRATIVE_ID_ENCODE(email_sha256, 'your_org_key_id') AS narrative_id
FROM company_data."123"

-- Create a dataset with encoded identifiers
SELECT
  NARRATIVE_ID_ENCODE(email_sha256, 'your_org_key_id') AS narrative_id,
  event_timestamp,
  event_properties
FROM company_data."123"
WHERE event_timestamp >= '2024-01-01'
```

<Note>
  The same identifier encoded with different `key_id` values produces different Narrative IDs. Always normalize identifiers (lowercase, trim whitespace) before encoding for consistent matching.
</Note>

**Related:** [Narrative ID Concepts](/concepts/security/narrative-id), [Using Narrative ID](/guides/collaboration/using-narrative-id)

### NARRATIVE\_ID\_TRANSLATE

Translates a Narrative ID from one partner's encoding space to another. This enables secure data collaboration by converting identifiers without exposing the underlying clear text values.

**Syntax:**

```sql theme={null}
NARRATIVE_ID_TRANSLATE(narrative_id, target_key_id)
```

**Parameters:**

| Parameter       | Type   | Description                                                 |
| --------------- | ------ | ----------------------------------------------------------- |
| `narrative_id`  | string | An existing Narrative ID to translate                       |
| `target_key_id` | string | The encoding key ID for the target partner's encoding space |

**Returns:** string - The translated Narrative ID in the target encoding space

**Examples:**

```sql theme={null}
-- Translate Narrative IDs for a partner collaboration
SELECT
  NARRATIVE_ID_TRANSLATE(narrative_id, 'partner_b_key_id') AS partner_b_narrative_id,
  customer_segment,
  activity_score
FROM company_data."123"

-- Join across partner datasets using translation
SELECT
  a.campaign_name,
  a.impressions,
  b.conversions
FROM partner_a."ad_data" a
JOIN partner_b."conversion_data" b
  ON NARRATIVE_ID_TRANSLATE(a.narrative_id, 'partner_b_key_id') = b.narrative_id

-- Prepare data for multiple partners
SELECT
  narrative_id AS original_id,
  NARRATIVE_ID_TRANSLATE(narrative_id, 'partner_a_key_id') AS partner_a_id,
  NARRATIVE_ID_TRANSLATE(narrative_id, 'partner_b_key_id') AS partner_b_id,
  audience_segment
FROM company_data."123"
```

**Use case:**

When sharing data with a partner, translate your Narrative IDs to their encoding space so they can match against their own Narrative IDs. This enables cross-organization matching without exposing underlying identifiers.

**Related:** [Narrative ID Concepts](/concepts/security/narrative-id), [Using Narrative ID](/guides/collaboration/using-narrative-id)

### UNIVERSE\_SAMPLE

Deterministic sampling based on an identifier.

```sql theme={null}
SELECT user_id, email, created_at
FROM company_data."123"
WHERE UNIVERSE_SAMPLE(user_id, 0.1)  -- 10% sample
```

### DETERMINISTIC\_RAND

Generates a deterministic random number from a seed.

```sql theme={null}
SELECT DETERMINISTIC_RAND(user_id) AS random_value
FROM company_data."123"
```

### PARSE\_JSON / TRY\_PARSE\_JSON

Parses a JSON string.

```sql theme={null}
SELECT PARSE_JSON('{"key": "value"}') AS parsed
SELECT TRY_PARSE_JSON(maybe_json) AS safe_parsed  -- Returns NULL on error
FROM company_data."123"
```

### OBJECT\_REMOVE\_NULLS

Returns a struct with null-valued fields removed. On AWS hosted data planes, the function is accepted for cross-platform query compatibility but operates as a no-op.

```sql theme={null}
SELECT OBJECT_REMOVE_NULLS(NAMED_STRUCT('a', 1, 'b', NULL, 'c', 3))
-- Input schema:  STRUCT<a: INT, b: INT, c: INT>
-- Output schema: STRUCT<a: INT, c: INT>
-- Result: {a: 1, c: 3}
```

### TOKENIZE

Tokenizes text using a specified model.

```sql theme={null}
SELECT TOKENIZE(text_column) AS tokens
FROM company_data."123"
```

### TOKENIZE\_SIZE

Returns the token count for text.

```sql theme={null}
SELECT TOKENIZE_SIZE(text_column) AS token_count
FROM company_data."123"
```

### EMBED\_TEXT\_768

Generates a 768-dimensional text embedding.

```sql theme={null}
SELECT EMBED_TEXT_768(description) AS embedding
FROM company_data."123"
```

### COSINE\_SIMILARITY

Calculates cosine similarity between two vectors.

```sql theme={null}
SELECT COSINE_SIMILARITY(embedding1, embedding2) AS similarity
FROM company_data."123"
```

### TEXT\_SEARCH

Scores text relevance against a search query.

```sql theme={null}
SELECT TEXT_SEARCH(description, 'machine learning') AS relevance_score
FROM company_data."123"
```

### UUID\_GENERATE

Generates a random UUID.

```sql theme={null}
SELECT UUID_GENERATE() AS new_id
```

***

## Related content

<CardGroup cols={2}>
  <Card title="Aggregate Functions" icon="chart-bar" href="/nql/functions/aggregate-functions">
    GROUP BY computations
  </Card>

  <Card title="SELECT" icon="magnifying-glass" href="/nql/commands/select">
    DELTA tables and budget clauses
  </Card>

  <Card title="All Functions" icon="function" href="/nql/functions/index">
    Browse all function categories
  </Card>

  <Card title="NQL Design Philosophy" icon="lightbulb" href="/concepts/nql/design-philosophy">
    Why NQL extends standard SQL
  </Card>
</CardGroup>