Categorias:

Funções de cadeia de caracteres e binários (Correspondência/Comparação)

SUBSTR , SUBSTRING

Retorna a parte da cadeia de caracteres ou valor binário de base_expr, começando pelo caractere/byte especificado por start_expr, com comprimento opcionalmente limitado.

Estas funções são sinônimas.

Consulte também:

LEFT , RIGHT

Sintaxe

SUBSTR( <base_expr>, <start_expr> [ , <length_expr> ] )

SUBSTRING( <base_expr>, <start_expr> [ , <length_expr> ] )
Copy

Argumentos

base_expr

Uma expressão que é avaliada como um valor VARCHAR ou BINARY.

start_expr

Uma expressão que é avaliada como um inteiro. Especifica o offset a partir do qual a subcadeia de caracteres começa. O offset é medido em:

  • O número de caracteres UTF-8 se a entrada for um valor VARCHAR.

  • O número de bytes se a entrada for um valor BINARY.

A posição inicial é baseada em 1, não em 0. Por exemplo, SUBSTR('abc', 1, 1) retorna a, não b.

length_expr

Uma expressão que é avaliada como um inteiro. Especifica:

  • O número de caracteres UTF-8 a retornar se a entrada for VARCHAR.

  • O número de bytes a retornar se a entrada for BINARY.

Especifique um comprimento maior ou igual a zero. Se o comprimento for um número negativo, a função retorna uma cadeia de caracteres vazia.

Retornos

O tipo de dados do valor retornado é o mesmo que o tipo de dados do base_expr (VARCHAR ou BINARY).

Se qualquer uma das entradas for NULL, NULL será retornado.

Notas de uso

  • Se length_expr for especificado, até length_expr caracteres/bytes serão retornados. Se length_expr não for especificado, todos os caracteres até o final da cadeia de caracteres ou valor binário serão retornados.

  • Os valores em start_expr começam a partir de 1:

    • Se 0 for especificado, ele será tratado como 1.

    • Se um valor negativo for especificado, a posição inicial é calculada como os start_expr caracteres/bytes a partir do final da cadeia de caracteres ou valor binário. Se a posição estiver fora do intervalo de uma cadeia de caracteres ou valor binário, um valor vazio é devolvido.

Detalhes do agrupamento

  • O agrupamento se aplica às entradas VARCHAR. O agrupamento não se aplica se o tipo de dados de entrada do primeiro parâmetro for BINARY.

  • No impact. Embora o agrupamento seja aceito sintaticamente, os agrupamentos não afetam o processamento. Por exemplo, letras de dois e três caracteres em idiomas (por exemplo, «dzs» em húngaro ou «ch» em tcheco) ainda são contadas como dois ou três caracteres (não um caractere) para o argumento de comprimento.

  • The collation of the result is the same as the collation of the input. Isso pode ser útil se o valor retornado for passado para outra função como parte de chamadas de funções aninhadas.

Exemplos

Os exemplos a seguir usam a função SUBSTR.

Exemplo básico

O exemplo a seguir usa a função SUBSTR para retornar a parte da cadeia de caracteres que começa no nono caractere e limita o comprimento do valor retornado a três caracteres:

SELECT SUBSTR('testing 1 2 3', 9, 3);
Copy
+-------------------------------+
| SUBSTR('TESTING 1 2 3', 9, 3) |
|-------------------------------|
| 1 2                           |
+-------------------------------+

Especificação de diferentes valores de início e comprimento

O exemplo a seguir mostra as substrings retornadas para a mesma base_expr quando valores diferentes são especificados para start_expr e length_expr:

CREATE OR REPLACE TABLE test_substr (
    base_value VARCHAR,
    start_value INT,
    length_value INT)
  AS SELECT
    column1,
    column2,
    column3
  FROM
    VALUES
      ('mystring', -1, 3),
      ('mystring', -3, 3),
      ('mystring', -3, 7),
      ('mystring', -5, 3),
      ('mystring', -7, 3),
      ('mystring', 0, 3),
      ('mystring', 0, 7),
      ('mystring', 1, 3),
      ('mystring', 1, 7),
      ('mystring', 3, 3),
      ('mystring', 3, 7),
      ('mystring', 5, 3),
      ('mystring', 5, 7),
      ('mystring', 7, 3),
      ('mystring', NULL, 3),
      ('mystring', 3, NULL);

SELECT base_value,
       start_value,
       length_value,
       SUBSTR(base_value, start_value, length_value) AS substring
  FROM test_substr;
Copy
+------------+-------------+--------------+-----------+
| BASE_VALUE | START_VALUE | LENGTH_VALUE | SUBSTRING |
|------------+-------------+--------------+-----------|
| mystring   |          -1 |            3 | g         |
| mystring   |          -3 |            3 | ing       |
| mystring   |          -3 |            7 | ing       |
| mystring   |          -5 |            3 | tri       |
| mystring   |          -7 |            3 | yst       |
| mystring   |           0 |            3 | mys       |
| mystring   |           0 |            7 | mystrin   |
| mystring   |           1 |            3 | mys       |
| mystring   |           1 |            7 | mystrin   |
| mystring   |           3 |            3 | str       |
| mystring   |           3 |            7 | string    |
| mystring   |           5 |            3 | rin       |
| mystring   |           5 |            7 | ring      |
| mystring   |           7 |            3 | ng        |
| mystring   |        NULL |            3 | NULL      |
| mystring   |           3 |         NULL | NULL      |
+------------+-------------+--------------+-----------+

Como retornar substrings para cadeias de caracteres de e-mail, telefone e data

Os exemplos a seguir retornam substrings para informações do cliente em uma tabela.

Crie a tabela e insira os dados:

CREATE OR REPLACE TABLE customer_contact_example (
    cust_id INT,
    cust_email VARCHAR,
    cust_phone VARCHAR,
    activation_date VARCHAR)
  AS SELECT
    column1,
    column2,
    column3,
    column4
  FROM
    VALUES
      (1, 'some_text@example.com', '800-555-0100', '20210320'),
      (2, 'some_other_text@example.org', '800-555-0101', '20240509'),
      (3, 'some_different_text@example.net', '800-555-0102', '20191017');

SELECT * from customer_contact_example;
Copy
+---------+---------------------------------+--------------+-----------------+
| CUST_ID | CUST_EMAIL                      | CUST_PHONE   | ACTIVATION_DATE |
|---------+---------------------------------+--------------+-----------------|
|       1 | some_text@example.com           | 800-555-0100 | 20210320        |
|       2 | some_other_text@example.org     | 800-555-0101 | 20240509        |
|       3 | some_different_text@example.net | 800-555-0102 | 20191017        |
+---------+---------------------------------+--------------+-----------------+

Use a função POSITION com a função SUBSTR para extrair os domínios dos endereços de e-mail. Este exemplo encontra a posição de @ em cada cadeia de caracteres e começa a partir do próximo caractere adicionando um:

SELECT cust_id,
       cust_email,
       SUBSTR(cust_email, POSITION('@' IN cust_email) + 1) AS domain
  FROM customer_contact_example;
Copy
+---------+---------------------------------+-------------+
| CUST_ID | CUST_EMAIL                      | DOMAIN      |
|---------+---------------------------------+-------------|
|       1 | some_text@example.com           | example.com |
|       2 | some_other_text@example.org     | example.org |
|       3 | some_different_text@example.net | example.net |
+---------+---------------------------------+-------------+

Dica

É possível usar a função POSITION para encontrar a posição de outros caracteres, como um caractere vazio (' ') ou um sublinhado (_).

Na coluna cust_phone da tabela, o código de área são sempre os três primeiros caracteres. Extraia o código de área dos números de telefone:

SELECT cust_id,
       cust_phone,
       SUBSTR(cust_phone, 1, 3) AS area_code
  FROM customer_contact_example;
Copy
+---------+--------------+-----------+
| CUST_ID | CUST_PHONE   | AREA_CODE |
|---------+--------------+-----------|
|       1 | 800-555-0100 | 800       |
|       2 | 800-555-0101 | 800       |
|       3 | 800-555-0102 | 800       |
+---------+--------------+-----------+

Remova o código de área dos números de telefone:

SELECT cust_id,
       cust_phone,
       SUBSTR(cust_phone, 5) AS phone_without_area_code
  FROM customer_contact_example;
Copy
+---------+--------------+-------------------------+
| CUST_ID | CUST_PHONE   | PHONE_WITHOUT_AREA_CODE |
|---------+--------------+-------------------------|
|       1 | 800-555-0100 | 555-0100                |
|       2 | 800-555-0101 | 555-0101                |
|       3 | 800-555-0102 | 555-0102                |
+---------+--------------+-------------------------+

Na coluna activation_date da tabela, a data está sempre no formato YYYYMMDD. Extraia o ano, mês e dia destas cadeias de caracteres:

SELECT cust_id,
       activation_date,
       SUBSTR(activation_date, 1, 4) AS year,
       SUBSTR(activation_date, 5, 2) AS month,
       SUBSTR(activation_date, 7, 2) AS day
  FROM customer_contact_example;
Copy
+---------+-----------------+------+-------+-----+
| CUST_ID | ACTIVATION_DATE | YEAR | MONTH | DAY |
|---------+-----------------+------+-------+-----|
|       1 | 20210320        | 2021 | 03    | 20  |
|       2 | 20240509        | 2024 | 05    | 09  |
|       3 | 20191017        | 2019 | 10    | 17  |
+---------+-----------------+------+-------+-----+
Linguagem: Português