1 of 35

String

This page describes how to use string functions in Lenses SQL Processors.

ABBREVIATE

This page describes the ABBREVIATE function in Lenses SQL.

ABBREVIATE(expr, lengthExpr)

Abbreviates the expression to the given length and appends ellipses.

Available in:

Processors

SQL Studio

Sample code:

USE `kafka`;
SELECT 
    ABBREVIATE(password_hash, 10), 
    password_hash 
FROM users-topic
LIMIT 1;

Output:

{
  "value": {
    "ABBREVIATE": "cc53c3b...",
    "password_hash": "cc53c3b3b0b966804573ceb917390510de95742d7caf3d7474872fd17f01f428"
  }
}

BASE64

This page describes the BASE64function in Lenses SQL.

Returns the input string using base64 algorithm.

Available in:

Processors

SQL Studio

Sample code:

Output:

CAPITALIZE

This page describes the CAPITALIZE function in Lenses SQL.

Capitalize the first letter of the expression.

Available in:

Processors

SQL Studio

Sample code:

Output:

CENTER

This page describes the CENTER function in Lenses SQL.

Centers a String in a larger String of size N.

Available in:

Processors

SQL Studio

Sample code:

Output:

CHOP

This page describes the CHOP function in Lenses SQL.

CHOP(expr)

Returns the last character from an expression of type string.

Available in:

Processors

SQL Studio

Sample code:

USE `kafka`;
SELECT 
    CHOP(last_name), 
    last_name 
FROM users-topic
LIMIT 1;

Output:

{
  "value": {
    "CHOP": "Hoo",
    "last_name": "Hood"
  }
}

CONCAT

This page describes the CONCAT function in Lenses SQL.

Returns the string representation of concatenating each expression in the list. Null fields are left out.

Available in:

Processors

SQL Studio

Sample code:

Output:

CONTAINS

This page describes the CONTAINS function in Lenses SQL.

Returns true if an expression contains the given substring.

Available in:

Processors

SQL Studio

Sample code:

Output:

DECODE64

This page describes the DECODE64 function in Lenses SQL.

DECODE64(expr)

Decodes a Base64 encrypted string.

Available in:

Processors

SQL Studio

Sample code:

USE `kafka`;
SELECT 
    DECODE64(password_hash),
    password_hash 
FROM users-topic
LIMIT 1;

Output:

{
  "value": {
    "DECODE64": "q�wsv�oF���4���q�������tu�y�����������;��u��5��",
    "password_hash": "cc53c3b3b0b966804573ceb917390510de95742d7caf3d7474872fd17f01f428"
  }
}

DELETEWHITESPACE

This page describes the DELETEWHITESPACE function in Lenses SQL.

DELETEWHITESPACE(expr)

Removes all whitespace from an expression of type string.

Available in:

Processors

SQL Studio

Sample code:

USE `kafka`;
SELECT 
    DELETEWHITESPACE(product_name), 
    product_name 
FROM products-topic
LIMIT 1;

Output:

{
  "value": {
    "DELETEWHITESPACE": "SoftShirt",
    "product_name": "Soft Shirt"
  }
}

DIGITS

This page describes the DIGITS function in Lenses SQL.

DIGITS(expr)

Retains only the digits from a string expression.

Available in:

Processors

SQL Studio

Sample code:

USE `kafka`;
SELECT 
    DIGITS(product_sku), 
    product_sku 
FROM products-topic
LIMIT 1;

Output:

{
  "value": {
    "DIGITS": "5021",
    "product_sku": "TOY-502-WH-1"
  }
}

DROPLEFT

This page describes the DROPLEFT function in Lenses SQL.

DROPLEFT(expr, lengthExpr)

Removes the left most ’length’ characters from a string expression.

Available in:

Processors

SQL Studio

Sample code:

USE `kafka`;
SELECT 
    DROPLEFT(password_hash, 50),
    password_hash 
FROM users-topic
LIMIT 1;

Output:

{
  "value": {
    "DROPLEFT": "872fd17f01f428",
    "password_hash": "cc53c3b3b0b966804573ceb917390510de95742d7caf3d7474872fd17f01f428"
  }
}

DROPRIGHT

This page describes the DROPRIGHT function in Lenses SQL.

DROPRIGHT(expr, lengthExpr)

Removes the rightmost ’length’ characters from a string expression.

Available in:

Processors

SQL Studio

Sample code:

USE `kafka`;
SELECT 
    DROPRIGHT(password_hash, 50),
    password_hash 
FROM users-topic
LIMIT 1;

Output:

{
  "value": {
    "DROPRIGHT": "f216651c7341b5",
    "password_hash": "f216651c7341b50cb69649c7bec2b11641f7af6ac0b35a046e1b1b852b84c903"
  }
}

ENDSWITH

This page describes the ENDSWITH function in Lenses SQL.

ENDSWITH(sourceExpr, targetExpr)

Returns true if an expression ends with the given substring.

Available in:

Processors

SQL Studio

Sample code:

USE `kafka`;
SELECT 
    ENDSWITH(status, 'unverified'), 
    status 
FROM users-topic
LIMIT 2;

Output:

{
  "value": {
    "ENDSWITH": false,
    "status": "verified"
  }
}
{
  "value": {
    "ENDSWITH": true,
    "status": "unverified"
  }
}

INDEXOF

This page describes the INDEXOF function in Lenses SQL.

INDEXOF(expr, substringExpr)

Returns the index of a substring in an expression.

Available in:

Processors

SQL Studio

Sample code:

USE `kafka`;
SELECT 
    INDEXOF(email,'@'), 
    email 
FROM users-topic
LIMIT 1;

Output:

{
  "value": {
    "INDEXOF": 9,
    "email": "Jose.Hood@aol.com"
  }
}

LEN

This page describes the LEN function in Lenses SQL.

LEN expr

Returns the length of a string. Calculates length using characters as defined by UTF-16.

Available in:

Processors

SQL Studio

Sample code:

USE `kafka`;
SELECT 
    LEN(email), 
    email 
FROM users-topic
LIMIT 1;

Output:

{
  "value": {
    "LEN": 17,
    "email": "Jose.Hood@aol.com"
  }
}

LOWER

This page describes the LOWER function in Lenses SQL.

LOWER(strExpr)

Returns the expression in lowercase.

Available in:

Processors

SQL Studio

Sample code:

USE `kafka`;
SELECT 
    LOWER(first_name), 
    first_name 
FROM users-topic
LIMIT 1;

Output:

{
  "value": {
    "LOWER": "jose",
    "first_name": "Jose"
  }
}

LPAD

This page describes the LPAD function in Lenses SQL.

LPAD(strExpr, lengthExpr, padExpr)

Prepends the value of padExpr to the value of strExpr until the total length is lengthExpr.

Available in:

Processors

SQL Studio

Sample code:

USE `kafka`;
SELECT 
    LPAD(email, 50, ' '), 
    email 
FROM users-topic
LIMIT 1;

Output:

{
  "value": {
    "LPAD": "                                 Jose.Hood@aol.com",
    "email": "Jose.Hood@aol.com"
  }
}

MKSTRING

This page describes the MKSTRING function in Lenses SQL.

MKSTRING(anyType)

Returns a string representation of any given value.

Available in:

Processors

SQL Studio

Sample code:

USE `kafka`;
SELECT 
    MKSTRING(tpep_dropoff_datetime), 
    dropoff_latitude 
FROM nyc-yellow-taxi-trip
LIMIT 1;

Output:

{
  "value": {
    "MKSTRING": "\"2016-01-01 00:11:14\"",
    "dropoff_latitude": "40.865577697753906"
  }
}

REGEXP

This page describes the REGEXP function in Lenses SQL.

REGEXP(strExpr, regexExpr)

Returns the matched groups otherwise null.

Available in:

Processors

SQL Studio

Sample code:

USE `kafka`;
SELECT 
    REGEXP(first_name,'Jo'), 
    first_name 
FROM users-topic
LIMIT 3;

Output:

{
  "value": {
    "REGEXP": [
      "Jo"
    ],
    "first_name": "Jose"
  }
}
{
  "value": {
    "REGEXP": [
      "Jo"
    ],
    "first_name": "Joshua"
  }
}
{
  "value": {
    "REGEXP": [],
    "first_name": "Kathleen"
  }
}

REGEX_MATCHES

This page describes the REGEX_MATCHES function in Lenses SQL.

REGEX_MATCHES(strExpr, regexExpr)

Returns an array of captured groups.

Available in:

Processors

SQL Studio

Sample code:

USE `kafka`;
SELECT 
    REGEX_MATCHES(first_name,'Jo'), 
    first_name 
FROM users-topic
LIMIT 2;

Output:

{
  "value": {
    "REGEX_MATCHES": [
      [
        "Jo"
      ]
    ],
    "first_name": "Jose"
  }
}
{
  "value": {
    "REGEX_MATCHES": [],
    "first_name": "Kathleen"
  }
}

REPLACE

This page describes the REPLACE function in Lenses SQL.

REPLACE (sourceExpr, targetExpr, replaceExpr)

Returns a new string in which all occurrences of a specified String in the current string are replaced with another specified String.

Available in:

Processors

SQL Studio

Sample code:

# REPLACE IS WITH ISSUE

USE `kafka`;
SELECT 
    REPLACE(status, 'unverified', 'verify urgent') 
FROM users-topic
LIMIT 100;

# Expected keyword FROM

Output:

// Some code

REVERSE

This page describes the REVERSE function in Lenses SQL.

REVERSE(expr)

Reverses the order of the elements in the input.

Available in:

Processors

SQL Studio

Sample code:

USE `kafka`;
SELECT 
    REVERSE(status), 
    status 
FROM users-topic
LIMIT 1;

Output:

{
  "value": {
    "REVERSE": "deifirevnu",
    "status": "unverified"
  }
}

RPAD

This page describes the REGEX_MATCHES function in Lenses SQL.

RPAD(strExpr, lengthExpr, padExpr)

Appends the value of padExpr to the value of strExpr until the total length is lengthExpr.

Available in:

Processors

SQL Studio

Sample code:

USE `kafka`;
SELECT 
    RPAD(email, 50, ' '), 
    email 
FROM users-topic
LIMIT 1;

Output:

{
  "value": {
    "RPAD": "Jose.Hood@aol.com                                 ",
    "email": "Jose.Hood@aol.com"
  }
}

STARTSWITH

This page describes the STARTSWITH function in Lenses SQL.

STARTSWITH(exprSource, exprTarget)

Returns true if an expression starts with the given substring.

Available in:

Processors

SQL Studio

Sample code:

USE `kafka`;
SELECT 
    STARTSWITH(email, 'Jo'), 
    email 
FROM users-topic
LIMIT 3;

Output:

{
  "value": {
    "STARTSWITH": true,
    "email": "Jose.Hood@aol.com"
  }
}
{
  "value": {
    "STARTSWITH": true,
    "email": "Joshua.Hernandez@aol.com"
  }
}
{
  "value": {
    "STARTSWITH": false,
    "email": "Kathleen.Williams@outlook.com"
  }
}

STRIPACCENTS

This page describes the STRIPACCENTS function in Lenses SQL.

STRIPACCENTS(expr)

Removes diacritics (approximately the same as accents) from an expression. The case will not be altered.

Available in:

Processors

SQL Studio

Sample code:

// Some code

Output:

// Some code

SUBSTR

This page describes the SUBSTR function in Lenses SQL.

SUBSTR(expr, startIndexExpr)

Returns a new string that is a substring of this string.

Available in:

Processors

SQL Studio

Sample code:

USE `kafka`;
SELECT 
    SUBSTR(email, 10 , 20),
    email 
FROM users-topic
LIMIT 1;

Output:

{
  "value": {
    "SUBSTR": "aol.com",
    "email": "Jose.Hood@aol.com"
  }
}

SWAPCASE

This page describes the SWAPCASE function in Lenses SQL.

SWAPCASE(expr)

Swaps the case of a string expression.

Available in:

Processors

SQL Studio

Sample code:

USE `kafka`;
SELECT 
    SWAPCASE(first_name), 
    first_name 
FROM users-topic
LIMIT 1;

Output:

{
  "value": {
    "SWAPCASE": "jOSE",
    "first_name": "Jose"
  }
}

TAKELEFT

This page describes the TAKELEFT function in Lenses SQL.

Returns the leftmost ’length’ characters from a string expression.

Available in:

Processors

SQL Studio

Sample code:

Output:

TAKERIGHT

This page describes the TAKERIGHT function in Lenses SQL.

TAKERIGHT(expr, lengthExpr)

Returns the rightmost ’length’ characters from a string expression

Available in:

Processors

SQL Studio

Sample code:

USE `kafka`;
SELECT 
    TAKERIGHT(first_name, 3), 
    first_name 
FROM users-topic
LIMIT 1;

Output:

{
  "value": {
    "TAKERIGHT": "ose",
    "first_name": "Jose"
  }
}

TRIM

This page describes the TRIM function in Lenses SQL.

TRIM(expr)

Removes leading and trailing spaces from the input expression.

Available in:

Processors

SQL Studio

Sample code:

USE `kafka`;
SELECT 
    LPAD(product_name, 50, ' '), 
    TRIM(LPAD(product_name, 50, ' ')), 
    product_name 
FROM products-topic
LIMIT 1;

Output:

{
  "value": {
    "LPAD": "                                        Soft Shirt",
    "TRIM": "Soft Shirt",
    "product_name": "Soft Shirt"
  }
}

LPAD function was used to emulate a space this way, using the TRIM function.

To know more about LPAD.

TRUNCATE

This page describes the TRUNCATE function in Lenses SQL.

TRUNCATE(strExpr, nExpr)

Truncates a string so that it has at most N characters.

Available in:

Processors

SQL Studio

Sample code:

USE `kafka`;
SELECT 
    `TRUNCATE`(first_name, 2), 
    first_name 
FROM users-topic
LIMIT 1;

Output:

{
  "value": {
    "TRUNCATE": "Jo",
    "first_name": "Jose"
  }
}

UNCAPITALIZE

This page describes the UNCAPITALIZE function in Lenses SQL.

UNCAPITALIZE(expr)

Change the first letter of each word in the expression to lowercase.

Available in:

Processors

SQL Studio

Sample code:

USE `kafka`;
SELECT 
    UNCAPITALIZE(first_name), 
    first_name 
FROM users-topic
LIMIT 1;

Output:

{
  "value": {
    "UNCAPITALIZE": "jose",
    "first_name": "Jose"
  }
}

UPPER

This page describes the UPPER function in Lenses SQL.

UPPER(expr)

Returns the expression in uppercase.

Available in:

Processors

SQL Studio

Sample code:

USE `kafka`;
SELECT 
    UPPER(first_name), 
    first_name 
FROM users-topic
LIMIT 1;

Output:

{
  "value": {
    "UPPER": "JOSE",
    "first_name": "Jose"
  }
}

UUID

This page describes the UUID function in Lenses SQL.

UUID(expr)

Returns a universally unique identifier.

Available in:

Processors

SQL Studio

Sample code:

USE `kafka`;
SELECT 
    UUID(),
    * 
FROM sea-vessel-position-reports
LIMIT 1;

Output:

{
  "key": {
    "MMSI": 257641900
  },
  "value": {
    "UUID": "1acbe81e-1af2-4c1f-a1b6-b5bd244378ab",
    "Type": 1,
    "Repeat": 0,
    "MMSI": 257641900,
    "Speed": 0,
    "Accuracy": true,
    "Longitude": "11.701588333333333",
    "Latitude": 58.007945,
    "location": "58.007945,11.701588",
    "Course": 0,
    "Heading": 174,
    "Second": 34,
    "RAIM": true,
    "Radio": 34075,
    "Status": 3,
    "Turn": 0,
    "Maneuver": 0,
    "Timestamp": "1503158676433679390"
  }
}