Essas funções de string são compatíveis com dois valores diferentes: tipos de dados STRING e BYTES. Os valores de STRING precisam ser UTF-8 bem formado.
As funções que retornam valores de posição, como STRPOS, codificam essas posições como INT64. O valor de 1
indica o primeiro caractere (ou byte), 2
o segundo, e assim por diante. O valor 0
indica um índice inválido. Ao trabalhar em tipos STRING, as posições retornadas indicam as posições dos caracteres.
Todas as comparações de string são feitas byte a byte, sem levar em conta a equivalência canônica Unicode.
BYTE_LENGTH
BYTE_LENGTH(value)
Descrição
Retorna o comprimento do valor em bytes, seja qual for o tipo de valor: STRING ou BYTES.
Tipo de retorno
INT64
Exemplos
WITH example AS
(SELECT "абвгд" AS characters, b"абвгд" AS bytes)
SELECT
characters,
BYTE_LENGTH(characters) AS string_example,
bytes,
BYTE_LENGTH(bytes) AS bytes_example
FROM example;
+------------+----------------+-------+---------------+
| characters | string_example | bytes | bytes_example |
+------------+----------------+-------+---------------+
| абвгд | 10 | абвгд | 10 |
+------------+----------------+-------+---------------+
CHAR_LENGTH
CHAR_LENGTH(value)
Descrição
Retorna o comprimento da STRING em caracteres.
Tipo de retorno
INT64
Exemplos
WITH example AS
(SELECT "абвгд" AS characters)
SELECT
characters,
CHAR_LENGTH(characters) AS char_length_example
FROM example;
+------------+---------------------+
| characters | char_length_example |
+------------+---------------------+
| абвгд | 5 |
+------------+---------------------+
CHARACTER_LENGTH
CHARACTER_LENGTH(value)
Descrição
Sinônimo de CHAR_LENGTH.
Tipo de retorno
INT64
Exemplos
WITH example AS
(SELECT "абвгд" AS characters)
SELECT
characters,
CHARACTER_LENGTH(characters) AS char_length_example
FROM example;
+------------+---------------------+
| characters | char_length_example |
+------------+---------------------+
| абвгд | 5 |
+------------+---------------------+
CODE_POINTS_TO_BYTES
CODE_POINTS_TO_BYTES(ascii_values)
Descrição
Toma uma matriz de pontos de código ASCII estendidos (ARRAY de INT64) e retorna BYTES.
Para converter de BYTES para uma matriz de pontos de código, consulte TO_CODE_POINTS.
Tipo de retorno
BYTES
Exemplos
Veja a seguir um exemplo básico usando CODE_POINTS_TO_BYTES
.
SELECT CODE_POINTS_TO_BYTES([65, 98, 67, 100]) AS bytes;
+-------+
| bytes |
+-------+
| AbCd |
+-------+
O exemplo a seguir utiliza um algoritmo ROT13 (rotacionar 13 posições) para codificar uma string.
SELECT CODE_POINTS_TO_BYTES(ARRAY_AGG(
(SELECT
CASE
WHEN chr BETWEEN b'a' and b'z'
THEN TO_CODE_POINTS(b'a')[offset(0)] +
MOD(code+13-TO_CODE_POINTS(b'a')[offset(0)],26)
WHEN chr BETWEEN b'A' and b'Z'
THEN TO_CODE_POINTS(b'A')[offset(0)] +
MOD(code+13-TO_CODE_POINTS(b'A')[offset(0)],26)
ELSE code
END
FROM
(SELECT code, CODE_POINTS_TO_BYTES([code]) chr)
) ORDER BY OFFSET)) AS encoded_string
FROM UNNEST(TO_CODE_POINTS(b'Test String!')) code WITH OFFSET;
+----------------+
| encoded_string |
+----------------+
| Grfg Fgevat! |
+----------------+
CODE_POINTS_TO_STRING
CODE_POINTS_TO_STRING(value)
Descrição
Toma uma série de pontos de código Unicode (ARRAY de INT64) e retorna STRING.
Para converter de uma string para uma matriz de pontos de código, consulte TO_CODE_POINTS.
Tipo de retorno
STRING
Exemplo
Veja a seguir um exemplo básico usando CODE_POINTS_TO_STRING
.
SELECT CODE_POINTS_TO_STRING([65, 255, 513, 1024]) AS string;
+--------+
| string |
+--------+
| AÿȁЀ |
+--------+
O exemplo a seguir calcula a frequência das letras em um grupo de palavras.
WITH Words AS (
SELECT word
FROM UNNEST(['foo', 'bar', 'baz', 'giraffe', 'llama']) AS word
)
SELECT
CODE_POINTS_TO_STRING([code_point]) AS letter,
COUNT(*) AS letter_count
FROM Words,
UNNEST(TO_CODE_POINTS(word)) AS code_point
GROUP BY 1
ORDER BY 2 DESC;
+--------+--------------+
| letter | letter_count |
+--------+--------------+
| a | 5 |
| f | 3 |
| r | 2 |
| b | 2 |
| l | 2 |
| o | 2 |
| g | 1 |
| z | 1 |
| e | 1 |
| m | 1 |
| i | 1 |
+--------+--------------+
CONCAT
CONCAT(value1[, ...])
Descrição
Concatena um ou mais valores em um único resultado.
Tipo de retorno
STRING ou BYTES
Exemplos
With Employees AS
(SELECT
"John" AS first_name,
"Doe" AS last_name
UNION ALL
SELECT
"Jane" AS first_name,
"Smith" AS last_name
UNION ALL
SELECT
"Joe" AS first_name,
"Jackson" AS last_name)
SELECT
CONCAT(first_name, " ", last_name)
AS full_name
FROM Employees;
+---------------------+
| full_name |
+---------------------+
| John Doe |
| Jane Smith |
| Joe Jackson |
+---------------------+
ENDS_WITH
ENDS_WITH(value1, value2)
Descrição
Toma dois valores. Retorna TRUE, se o segundo valor for um sufixo do primeiro.
Tipo de retorno
BOOL
Exemplos
WITH items AS
(SELECT "apple" as item
UNION ALL
SELECT "banana" as item
UNION ALL
SELECT "orange" as item)
SELECT
ENDS_WITH(item, "e") as example
FROM items;
+---------+
| example |
+---------+
| True |
| False |
| True |
+---------+
FORMAT
O BigQuery é compatível com a função FORMAT()
para o formato das strings. Essa função é semelhante à printf
do C. Ela produz uma STRING de uma string de formato que contém zero ou mais especificadores de formato, juntamente com uma lista de comprimentos variáveis de argumentos adicionais que correspondem aos especificadores. Veja alguns exemplos:
Descrição | Declaração | Resultado |
---|---|---|
Inteiro simples | format("%d", 10) | 10 |
Inteiro com preenchimento vazio à esquerda | format("|%10d|", 11) | | 11| |
Inteiro com preenchimento com zero à esquerda | format("+%010d+", 12) | +0000000012+ |
Inteiro com vírgulas | format("%'d", 123456789) | 123,456,789 |
STRING | format("-%s-", 'abcd efg') | -abcd efg- |
FLOAT64 | format("%f %E", 1.1, 2.2) | 1.100000 2.200000E+00 |
DATE | format("%t", date "2015-09-01") | 2015-09-01 |
TIMESTAMP | format("%t", timestamp "2015-09-01 12:34:56 America/Los_Angeles") | 2015‑09‑01 19:34:56+00 |
A função FORMAT()
não oferece formato totalmente personalizável para todos os tipos e valores, nem formato sensível à localidade.
Se o formato personalizado for necessário para um tipo, primeiro formate-o usando as funções específicas do tipo, como FORMAT_DATE()
ou FORMAT_TIMESTAMP()
.
Por exemplo:
FORMAT("date: %s!", FORMAT_DATE("%B %d, %Y", date '2015-01-02'));
Retorna
date: January 02, 2015!
Sintaxe
A sintaxe FORMAT()
toma uma lista de strings de formato e comprimentos variáveis dos argumentos e produz um resultado STRING:
FORMAT(<format_string>, ...)
A expressão <format_string>
pode conter zero ou mais especificadores de formato.
Cada especificador de formato é introduzido pelo símbolo %
e precisa ser mapeado para um ou mais dos argumentos restantes. Em geral, esse é um mapeamento um para um, exceto quando o especificador *
está presente. Por exemplo, %.*i
é mapeado para dois argumentos: de comprimento e de inteiro com sinal. Se o número de argumentos relacionados com os especificadores de formato não for o mesmo número de argumentos, ocorrerá um erro.
Especificadores de formatos compatíveis
O especificador de formato da função FORMAT()
segue este protótipo:
%[flags][width][.precision]specifier
Os especificadores de formatos compatíveis são identificados na tabela a seguir. As extensões de printf() são identificadas em itálico.
Especificador | Descrição | Exemplos | Tipos |
d ou i |
Decimal inteiro | 392 |
INT64 |
o |
Octal | 610 |
INT64* |
x |
Inteiro hexadecimal | 7fa |
INT64* |
X |
Inteiro hexadecimal (maiúsculas) | 7FA |
INT64* |
f |
Ponto flutuante decimal, minúsculas | 392,65inf NaN |
NUMERIC FLOAT64 |
F |
Ponto flutuante decimal, maiúsculas | 392,65inf NAN |
NUMERIC FLOAT64 |
e |
Notação científica (mantissa/expoente), minúsculas | 3,9265e+2inf NaN |
NUMERIC FLOAT64 |
E |
Notação científica (mantissa/expoente), maiúsculas | 3,9265E+2inf NAN |
NUMERIC FLOAT64 |
g |
Usa a menor representação, %e ou %f | 392,65 |
FLOAT64 |
G |
Usa a menor representação, %E ou %F | 392,65 |
FLOAT64 |
s |
String de caracteres | amostra | STRING |
t |
Retorna uma string para impressão que representa o valor. Costuma ser semelhante à transmissão do argumento para STRING. Consulte a seção %t abaixo. | amostra 2014‑01‑01 |
<qualquer> |
T |
Produz uma string que é uma constante válida do BigQuery, com um tipo semelhante ao do valor (talvez mais amplo ou string). Consulte a seção %T abaixo. | 'amostra' b'bytes amostra' 1234 2,3 data '2014‑01‑01' |
<qualquer> |
% |
'%%' produz um único '%' | % | n/d |
*Os especificadores o, x e X geram um erro com o uso de valores negativos.
O formato do especificador também pode conter os subespecificadores identificados acima no protótipo do especificador.
Esses subespecificadores precisam seguir estas especificações.
Sinalizações
Sinalizações | Descrição |
- |
Justificado à esquerda com a largura do campo. O padrão é a justificação à direita (consulte sobre subespecificador de largura) |
+ |
Força a preceder o resultado com um sinal de mais ou menos (+ ou - ), incluindo números positivos. Por padrão, apenas os números negativos são precedidos por um sinal - |
<space> | Se nenhum sinal for gravado, um espaço em branco será inserido antes do valor |
# |
Usado com os especificadores o, x ou X. Precede valores diferentes de zero com 0, 0x ou 0X, respectivamente |
0 |
Quando o preenchimento é especificado, coloca zeros (0) à esquerda do número, em vez de espaços (consulte sobre subespecificador de largura) |
' |
Formata inteiros usando o caractere de agrupamento adequado. Por exemplo:
Essa sinalização só é relevante para valores decimais, hexadecimais e octais. |
As sinalizações podem ser especificadas em qualquer ordem. As sinalizações duplicadas não são um erro. As sinalizações são ignoradas quando não são relevantes para algum tipo de elemento.
Largura
Largura | Descrição |
<número> | O número mínimo de caracteres a ser impresso. Se o valor a ser impresso for mais curto do que esse número, o resultado será preenchido com espaços em branco. O valor não é truncado mesmo que o resultado seja maior |
* |
A largura não é especificada na string de formato, mas como um argumento de valor inteiro complementar precedendo o argumento que precisa ser formatado |
Precisão
Precisão | Descrição |
. <number> |
Para os especificadores inteiros (d, i, o, u, X, X): a precisão especifica o número mínimo de dígitos a serem gravados. Se o valor a ser gravado for menor que esse número, o resultado será preenchido com zeros à direita. O valor não será truncado mesmo que o resultado seja maior. Uma precisão de 0 significa que nenhum caractere é gravado para o valor 0. Para os especificadores a, A, e, E, f e F: esse é o número de dígitos a serem impressos depois do ponto decimal (por padrão, é 6) |
.* |
A precisão não é especificada na string de formato, mas como um argumento de valor inteiro complementar precedendo o argumento que precisa ser formatado |
Comportamento de %t e %T
Os especificadores de formato %t
e %T
são definidos para todos os tipos. A largura, a precisão e as sinalizações agem da mesma forma que para %s
: width
é a largura mínima e STRING será preenchido com esse tamanho. precision
é a largura máxima do conteúdo a ser exibido e a STRING será truncada com esse tamanho, antes de preencher a largura.
%t sempre será uma forma legível do valor.
%T é sempre um literal SQL válido de um tipo semelhante, como um tipo numérico mais amplo. O literal não inclui casts ou um nome de tipo, exceto no caso especial de valores de ponto flutuante não finitos.
A STRING é formatada como a seguir:
Tipo | %t | %T |
NULL de qualquer tipo |
NULL |
NULL |
INT64 |
123 | 123 |
NUMERIC | 123,0 (sempre com ,0) | NUMERIC "123,0" |
FLOAT64 | 123,0 (sempre com ,0) 123e+10 inf -inf NaN |
123,0 (sempre com ,0) 123e+10 CAST("inf" AS <tipo>) CAST("-inf" AS <tipo>) CAST("nan" AS <tipo>) |
STRING | valor da string sem aspas | literal da string com aspas |
BYTES | bytes com caractere de escape sem aspas por exemplo, abc\x01\x02 |
literal de bytes com aspas por exemplo, b"abc\x01\x02" |
DATE | 2011-02-03 | DATE "2011-02-03" |
TIMESTAMP | 2011-02-03 04:05:06+00 | TIMESTAMP "2011-02-03 04:05:06+00" |
ARRAY | [valor, valor, ...] no qual os valores são formatados com %t |
[valor, valor, ...] no qual os valores são formatados com %T |
STRUCT | (valor, valor, ...) no qual os campos são formatados com %t |
(valor, valor, ...) no qual os campos são formatados com %T Casos especiais: Campos com zero: STRUCT() Um campo: STRUCT(valor) |
Condições de erro
Se um especificador de formato for inválido ou não compatível com o tipo de argumento relacionado, ou forem fornecidos o número ou os argumentos incorretos, ocorrerá um erro. Por exemplo, as expressões <format_string>
a seguir são inválidas:
FORMAT('%s', 1)
FORMAT('%')
Tratamento do argumento NULL
Uma string de formato NULL
resulta em uma STRING de saída NULL
. Outros argumentos são ignorados, neste caso.
Em geral, a função produz um valor NULL
se um argumento NULL
estiver presente. Por exemplo, FORMAT('%i', <NULL expression>)
produz uma STRING NULL
como saída.
No entanto, há algumas exceções: se o especificador de formato for %t ou %T (que produzem STRINGs que efetivamente correspondem à semântica do valor do literal e CAST), um valor NULL
produzirá 'NULL' (sem as aspas) na STRING de resultado. Por exemplo, a função:
FORMAT('00-%t-00', <NULL expression>);
Retorna
00-NULL-00
Regras semânticas adicionais
Os valores FLOAT64 podem ser +/-inf
ou NaN
. Quando um argumento tem um desses valores, o resultado dos especificadores de formato %f
, %F
, %e
, %E
, %g
, %G
e %t
são inf
, -inf
ou nan
(ou o mesmo em maiúsculas), como apropriado. Isto é consistente com a forma como o BigQuery faz o cast desses valores para STRING. Para %T
, o BigQuery retorna strings com aspas dos valores FLOAT64 sem representações de literal que não sejam strings.
FROM_BASE32
FROM_BASE32(string_expr)
Descrição
Converte a entrada string_expr
codificada como base32 no formato BYTES. Para converter BYTES em uma string codificada em base32, use TO_BASE32().
Tipo de retorno
BYTES
Exemplo
SELECT FROM_BASE32('MFRGGZDF74======') AS byte_data;
+-----------+
| byte_data |
+-----------+
| abcde\xff |
+-----------+
FROM_BASE64
FROM_BASE64(string_expr)
Descrição
Converte a entrada string_expr
codificada como base64 para o formato BYTES. Para converter BYTES para uma STRING codificada como base64, use TO_BASE64.
Tipo de retorno
BYTES
Exemplo
SELECT FROM_BASE64('3q2+7w==') AS byte_data;
+------------------+
| byte_data |
+------------------+
| \xde\xad\xbe\xef |
+------------------+
FROM_HEX
FROM_HEX(string)
Descrição
Converte um STRING codificado em hexadecimal em formato BYTES. Um erro será retornado se a STRING de entrada contiver caracteres fora do intervalo (0..9, A..F, a..f)
. Não importa se os caracteres estão em maiúsculas ou minúsculas. Se a STRING de entrada tiver um número ímpar de caracteres, a função agirá como se a entrada tivesse um 0
à esquerda. Para converter BYTES em uma STRING codificada como hexadecimal, use TO_HEX.
Tipo de retorno
BYTES
Exemplo
WITH Input AS (
SELECT '00010203aaeeefff' AS hex_str UNION ALL
SELECT '0AF' UNION ALL
SELECT '666f6f626172'
)
SELECT hex_str, FROM_HEX(hex_str) AS bytes_str
FROM Input;
+------------------+----------------------------------+
| hex_str | bytes_str |
+------------------+----------------------------------+
| 0AF | \x00\xaf |
| 00010203aaeeefff | \x00\x01\x02\x03\xaa\xee\xef\xff |
| 666f6f626172 | foobar |
+------------------+----------------------------------+
LENGTH
LENGTH(value)
Descrição
Retorna o comprimento do valor. O valor retornado está em caracteres para argumentos STRING e em bytes para o argumento BYTES.
Tipo de retorno
INT64
Exemplos
WITH example AS
(SELECT "абвгд" AS characters)
SELECT
characters,
LENGTH(characters) AS string_example,
LENGTH(CAST(characters AS BYTES)) AS bytes_example
FROM example;
+------------+----------------+---------------+
| characters | string_example | bytes_example |
+------------+----------------+---------------+
| абвгд | 5 | 10 |
+------------+----------------+---------------+
LPAD
LPAD(original_value, return_length[, pattern])
Descrição
Retorna um valor que consiste em original_value
anexado ao pattern
. O return_length
é um INT64 que especifica o comprimento do valor retornado. Se original_value
for BYTES, return_length
será o número de bytes. Se original_value
for STRING, return_length
será o número de caracteres.
O valor padrão de pattern
é um espaço em branco.
O tipo de dados de original_value
e pattern
precisa ser o mesmo.
Se return_length
for menor que ou igual ao comprimento original_value
, a função retornará o valor original_value
truncado para o valor de return_length
. Por exemplo, LPAD("hello world", 7);
retorna "hello w"
.
Se original_value
, return_length
ou pattern
for NULL, a função retornará NULL.
Essa função retornará um erro se:
return_length
for negativo;pattern
estiver vazio.
Tipo de retorno
STRING ou BYTES
Exemplos
SELECT t, len, FORMAT("%T", LPAD(t, len)) AS LPAD FROM UNNEST([
STRUCT('abc' AS t, 5 AS len),
('abc', 2),
('例子', 4)
]);
t | len | LPAD |
---|---|---|
abc | 5 | " abc" |
abc | 2 | "ab" |
例子 | 4 | " 例子" |
SELECT t, len, pattern, FORMAT("%T", LPAD(t, len, pattern)) AS LPAD FROM UNNEST([
STRUCT('abc' AS t, 8 AS len, 'def' AS pattern),
('abc', 5, '-'),
('例子', 5, '中文')
]);
t | len | padrão | LPAD |
---|---|---|---|
abc | 8 | def | "defdeabc" |
abc | 5 | - | "--abc" |
例子 | 5 | 中文 | "中文中例子" |
SELECT FORMAT("%T", t) AS t, len, FORMAT("%T", LPAD(t, len)) AS LPAD FROM UNNEST([
STRUCT(b'abc' AS t, 5 AS len),
(b'abc', 2),
(b'\xab\xcd\xef', 4)
]);
t | len | LPAD |
---|---|---|
b"abc" | 5 | b" abc" |
b"abc" | 2 | b"ab" |
b"\xab\xcd\xef" | 4 | b" \xab\xcd\xef" |
SELECT
FORMAT("%T", t) AS t,
len,
FORMAT("%T", pattern) AS pattern,
FORMAT("%T", LPAD(t, len, pattern)) AS LPAD
FROM UNNEST([
STRUCT(b'abc' AS t, 8 AS len, b'def' AS pattern),
(b'abc', 5, b'-'),
(b'\xab\xcd\xef', 5, b'\x00')
]);
t | len | padrão | LPAD |
---|---|---|---|
b"abc" | 8 | b"def" | b"defdeabc" |
b"abc" | 5 | b"-" | b"--abc" |
b"\xab\xcd\xef" | 5 | b"\x00" | b"\x00\x00\xab\xcd\xef" |
LOWER
LOWER(value)
Descrição
Para argumentos STRING, retorna a string original com todos os caracteres alfabéticos em minúsculas. O mapeamento entre maiúsculas e minúsculas é feito de acordo com o banco de dados de caracteres Unicode. Os mapeamentos específicos do idioma não são considerados.
Para argumentos BYTES, o argumento é tratado como texto ASCII, com todos os bytes superiores a 127 intactos.
Tipo de retorno
STRING ou BYTES
Exemplos
WITH items AS
(SELECT
"FOO" as item
UNION ALL
SELECT
"BAR" as item
UNION ALL
SELECT
"BAZ" as item)
SELECT
LOWER(item) AS example
FROM items;
+---------+
| example |
+---------+
| foo |
| bar |
| baz |
+---------+
LTRIM
LTRIM(value1[, value2])
Descrição
Idêntico a TRIM, mas remove apenas os caracteres à esquerda.
Tipo de retorno
STRING ou BYTES
Exemplos
WITH items AS
(SELECT " apple " as item
UNION ALL
SELECT " banana " as item
UNION ALL
SELECT " orange " as item)
SELECT
CONCAT("#", LTRIM(item), "#") as example
FROM items;
+-------------+
| example |
+-------------+
| #apple # |
| #banana # |
| #orange # |
+-------------+
WITH items AS
(SELECT "***apple***" as item
UNION ALL
SELECT "***banana***" as item
UNION ALL
SELECT "***orange***" as item)
SELECT
LTRIM(item, "*") as example
FROM items;
+-----------+
| example |
+-----------+
| apple*** |
| banana*** |
| orange*** |
+-----------+
WITH items AS
(SELECT "xxxapplexxx" as item
UNION ALL
SELECT "yyybananayyy" as item
UNION ALL
SELECT "zzzorangezzz" as item
UNION ALL
SELECT "xyzpearxyz" as item)
SELECT
LTRIM(item, "xyz") as example
FROM items;
+-----------+
| example |
+-----------+
| applexxx |
| bananayyy |
| orangezzz |
| pearxyz |
+-----------+
NORMALIZE
NORMALIZE(value[, normalization_mode])
Descrição
Toma uma STRING, value
e retorna como uma string normalizada.
A normalização é utilizada para garantir que duas strings sejam equivalentes. Em geral, a normalização é usada em situações em que duas strings renderizam da mesma maneira na tela, mas têm diferentes pontos de código Unicode.
NORMALIZE
é compatível com quatro modos de normalização opcionais:
Valor | Nome | Descrição |
---|---|---|
NFC | Composição canônica do Formulário de normalização | Decompõe e recompõe caracteres por equivalência canônica. |
NFKC | Composição de Compatibilidade do Formulário de Normalização | Decompõe caracteres por compatibilidade e os recompõe por equivalência canônica. |
NFD | Decomposição canônica do Formulário de normalização | Decompõe caracteres por equivalência canônica. Vários caracteres de combinação são organizados em uma ordem específica. |
NFKD | Decomposição de compatibilidade do Formulário de normalização | Decompõe caracteres por compatibilidade. Vários caracteres de combinação são organizados em uma ordem específica. |
O modo de normalização padrão é NFC
.
Tipo de retorno
STRING
Exemplos
SELECT a, b, a = b as normalized
FROM (SELECT NORMALIZE('\u00ea') as a, NORMALIZE('\u0065\u0302') as b)
AS normalize_example;
+---+---+------------+
| a | b | normalized |
+---+---+------------+
| ê | ê | true |
+---+---+------------+
O exemplo a seguir normaliza diferentes caracteres de espaços.
WITH EquivalentNames AS (
SELECT name
FROM UNNEST([
'Jane\u2004Doe',
'John\u2004Smith',
'Jane\u2005Doe',
'Jane\u2006Doe',
'John Smith']) AS name
)
SELECT
NORMALIZE(name, NFKC) AS normalized_name,
COUNT(*) AS name_count
FROM EquivalentNames
GROUP BY 1;
+-----------------+------------+
| normalized_name | name_count |
+-----------------+------------+
| John Smith | 2 |
| Jane Doe | 3 |
+-----------------+------------+
NORMALIZE_AND_CASEFOLD
NORMALIZE_AND_CASEFOLD(value[, normalization_mode])
Descrição
Utiliza uma STRING, value
, e executa as mesmas ações de NORMALIZE
, bem como o casefold (em inglês) de operações que não diferenciam maiúsculas e minúsculas.
NORMALIZE_AND_CASEFOLD
é compatível com quatro modos de normalização opcionais:
Valor | Nome | Descrição |
---|---|---|
NFC | Composição canônica do Formulário de normalização | Decompõe e recompõe caracteres por equivalência canônica. |
NFKC | Composição de Compatibilidade do Formulário de Normalização | Decompõe caracteres por compatibilidade e os recompõe por equivalência canônica. |
NFD | Decomposição canônica do Formulário de normalização | Decompõe caracteres por equivalência canônica. Vários caracteres de combinação são organizados em uma ordem específica. |
NFKD | Decomposição de compatibilidade do Formulário de normalização | Decompõe caracteres por compatibilidade. Vários caracteres de combinação são organizados em uma ordem específica. |
O modo de normalização padrão é NFC
.
Tipo de retorno
STRING
Exemplo
WITH Strings AS (
SELECT '\u2168' AS a, 'IX' AS b UNION ALL
SELECT '\u0041\u030A', '\u00C5'
)
SELECT a, b,
NORMALIZE_AND_CASEFOLD(a, NFD)=NORMALIZE_AND_CASEFOLD(b, NFD) AS nfd,
NORMALIZE_AND_CASEFOLD(a, NFC)=NORMALIZE_AND_CASEFOLD(b, NFC) AS nfc,
NORMALIZE_AND_CASEFOLD(a, NFKD)=NORMALIZE_AND_CASEFOLD(b, NFKD) AS nkfd,
NORMALIZE_AND_CASEFOLD(a, NFKC)=NORMALIZE_AND_CASEFOLD(b, NFKC) AS nkfc
FROM Strings;
+---+----+-------+-------+------+------+
| a | b | nfd | nfc | nkfd | nkfc |
+---+----+-------+-------+------+------+
| Ⅸ | IX | false | false | true | true |
| Å | Å | true | true | true | true |
+---+----+-------+-------+------+------+
REGEXP_CONTAINS
REGEXP_CONTAINS(value, regex)
Descrição
Retorna TRUE se value
for uma correspondência parcial da expressão regular regex
.
Se o argumento regex
for inválido, a função retornará um erro.
É possível pesquisar uma correspondência total usando ^
(início do texto) e
$
(fim do texto). Devido à precedência do operador de expressão regular, é recomendável usar parênteses em torno de tudo entre ^
e $
.
Tipo de retorno
BOOL
Exemplos
SELECT
email,
REGEXP_CONTAINS(email, r"@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.]+") AS is_valid
FROM
(SELECT
["foo@example.com", "bar@example.org", "www.example.net"]
AS addresses),
UNNEST(addresses) AS email;
+-----------------+----------+
| email | is_valid |
+-----------------+----------+
| foo@example.com | true |
| bar@example.org | true |
| www.example.net | false |
+-----------------+----------+
# Performs a full match, using ^ and $. Due to regular expression operator
# precedence, it is good practice to use parentheses around everything between ^
# and $.
SELECT
email,
REGEXP_CONTAINS(email, r"^([\w.+-]+@foo\.com|[\w.+-]+@bar\.org)$")
AS valid_email_address,
REGEXP_CONTAINS(email, r"^[\w.+-]+@foo\.com|[\w.+-]+@bar\.org$")
AS without_parentheses
FROM
(SELECT
["a@foo.com", "a@foo.computer", "b@bar.org", "!b@bar.org", "c@buz.net"]
AS addresses),
UNNEST(addresses) AS email;
+----------------+---------------------+---------------------+
| email | valid_email_address | without_parentheses |
+----------------+---------------------+---------------------+
| a@foo.com | true | true |
| a@foo.computer | false | true |
| b@bar.org | true | true |
| !b@bar.org | false | true |
| c@buz.net | false | false |
+----------------+---------------------+---------------------+
REGEXP_EXTRACT
REGEXP_EXTRACT(value, regex)
Descrição
Retorna a primeira substring em value
que corresponde à expressão regular regex
. Retorna NULL
, se não houver correspondência.
Se a expressão regular contiver um grupo de captura, a função retornará a substring que corresponde ao grupo de captura. Se a expressão não contiver um grupo de captura, a função retornará toda a substring correspondente.
Retorna um erro se:
- a expressão regular for inválida;
- a expressão regular tiver mais de um grupo de captura.
Tipo de retorno
STRING ou BYTES
Exemplos
WITH email_addresses AS
(SELECT "foo@example.com" as email
UNION ALL
SELECT "bar@example.org" as email
UNION ALL
SELECT "baz@example.net" as email)
SELECT
REGEXP_EXTRACT(email, r"^[a-zA-Z0-9_.+-]+")
AS user_name
FROM email_addresses;
+-----------+
| user_name |
+-----------+
| foo |
| bar |
| baz |
+-----------+
WITH email_addresses AS
(SELECT "foo@example.com" as email
UNION ALL
SELECT "bar@example.org" as email
UNION ALL
SELECT "baz@example.net" as email)
SELECT
REGEXP_EXTRACT(email, r"^[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\.([a-zA-Z0-9-.]+$)")
AS top_level_domain
FROM email_addresses;
+------------------+
| top_level_domain |
+------------------+
| com |
| org |
| net |
+------------------+
REGEXP_EXTRACT_ALL
REGEXP_EXTRACT_ALL(value, regex)
Descrição
Retorna uma matriz de todas as substrings de value
que correspondem à expressão regular regex
.
A função REGEXP_EXTRACT_ALL
retorna apenas as correspondências não sobrepostas. Por exemplo, o uso dessa função para extrair ana
de banana
retorna somente uma
substring, não duas.
Tipo de retorno
Uma ARRAY de STRINGs ou BYTES
Exemplos
WITH code_markdown AS
(SELECT "Try `function(x)` or `function(y)`" as code)
SELECT
REGEXP_EXTRACT_ALL(code, "`(.+?)`") AS example
FROM code_markdown;
+----------------------------+
| example |
+----------------------------+
| [function(x), function(y)] |
+----------------------------+
REGEXP_REPLACE
REGEXP_REPLACE(value, regex, replacement)
Descrição
Retorna uma STRING em que todas as substrings de value
que correspondem à expressão regular regex
são substituídas por replacement
.
Use dígitos entre barras invertidas (\1 a \9) no argumento replacement
para inserir texto correspondente ao grupo entre parênteses no padrão
regex
. Use \0 para indicar o texto correspondente inteiro.
A função REGEXP_REPLACE
substitui apenas as correspondências não sobrepostas. Por exemplo, substituir ana
em banana
resulta em apenas uma substituição, não duas.
Se o argumento regex
não for uma expressão regular válida, essa função retornará um erro.
Tipo de retorno
STRING ou BYTES
Exemplos
WITH markdown AS
(SELECT "# Heading" as heading
UNION ALL
SELECT "# Another heading" as heading)
SELECT
REGEXP_REPLACE(heading, r"^# ([a-zA-Z0-9\s]+$)", "<h1>\\1</h1>")
AS html
FROM markdown;
+--------------------------+
| html |
+--------------------------+
| <h1>Heading</h1> |
| <h1>Another heading</h1> |
+--------------------------+
REPLACE
REPLACE(original_value, from_value, to_value)
Descrição
Substitui todas as ocorrências de from_value
por to_value
em original_value
.
Se from_value
estiver vazio, nenhuma substituição será realizada.
Tipo de retorno
STRING ou BYTES
Exemplos
WITH desserts AS
(SELECT "apple pie" as dessert
UNION ALL
SELECT "blackberry pie" as dessert
UNION ALL
SELECT "cherry pie" as dessert)
SELECT
REPLACE (dessert, "pie", "cobbler") as example
FROM desserts;
+--------------------+
| example |
+--------------------+
| apple cobbler |
| blackberry cobbler |
| cherry cobbler |
+--------------------+
REPEAT
REPEAT(original_value, repetitions)
Descrição
Retorna um valor que consiste em original_value
, repetido.
O parâmetro repetitions
especifica o número de repetições de original_value
. Retorna NULL se original_value
ou repetitions
forem NULL.
Essa função retornará um erro se o valor de repetitions
for negativo.
Tipo de retorno
STRING ou BYTES
Exemplos
SELECT t, n, REPEAT(t, n) AS REPEAT FROM UNNEST([
STRUCT('abc' AS t, 3 AS n),
('例子', 2),
('abc', null),
(null, 3)
]);
t | n | REPEAT |
---|---|---|
abc | 3 | abcabcabc |
例子 | 2 | 例子例子 |
abc | NULL | NULL |
NULL | 3 | NULL |
REVERSE
REVERSE(value)
Descrição
Retorna o inverso de STRING ou BYTES da entrada.
Tipo de retorno
STRING ou BYTES
Exemplos
WITH example AS (
SELECT "foo" AS sample_string, b"bar" AS sample_bytes UNION ALL
SELECT "абвгд" AS sample_string, b"123" AS sample_bytes
)
SELECT
sample_string,
REVERSE(sample_string) AS reverse_string,
sample_bytes,
REVERSE(sample_bytes) AS reverse_bytes
FROM example;
+---------------+----------------+--------------+---------------+
| sample_string | reverse_string | sample_bytes | reverse_bytes |
+---------------+----------------+--------------+---------------+
| foo | oof | bar | rab |
| абвгд | дгвба | 123 | 321 |
+---------------+----------------+--------------+---------------+
RPAD
RPAD(original_value, return_length[, pattern])
Descrição
Retorna um valor que consiste em original_value
anexado a pattern
. O return_length
é um INT64 que especifica o comprimento do valor retornado. Se original_value
for BYTES, return_length
será o número de bytes. Se original_value
for STRING, return_length
será o número de caracteres.
O valor padrão de pattern
é um espaço em branco.
O tipo de dados de original_value
e pattern
precisa ser o mesmo.
Se return_length
for menor que ou igual ao comprimento original_value
, a função retornará o valor original_value
truncado para o valor de return_length
. Por exemplo, RPAD("hello world", 7);
retorna "hello w"
.
Se original_value
, return_length
ou pattern
for NULL, a função retornará NULL.
Essa função retornará um erro se:
return_length
for negativo;pattern
estiver vazio.
Tipo de retorno
STRING ou BYTES
Exemplos
SELECT t, len, FORMAT("%T", RPAD(t, len)) AS RPAD FROM UNNEST([
STRUCT('abc' AS t, 5 AS len),
('abc', 2),
('例子', 4)
]);
t | len | RPAD |
---|---|---|
abc | 5 | "abc " |
abc | 2 | "ab" |
例子 | 4 | "例子 " |
SELECT t, len, pattern, FORMAT("%T", RPAD(t, len, pattern)) AS RPAD FROM UNNEST([
STRUCT('abc' AS t, 8 AS len, 'def' AS pattern),
('abc', 5, '-'),
('例子', 5, '中文')
]);
t | len | padrão | RPAD |
---|---|---|---|
abc | 8 | def | "abcdefde" |
abc | 5 | - | "abc--" |
例子 | 5 | 中文 | "例子中文中" |
SELECT FORMAT("%T", t) AS t, len, FORMAT("%T", RPAD(t, len)) AS RPAD FROM UNNEST([
STRUCT(b'abc' AS t, 5 AS len),
(b'abc', 2),
(b'\xab\xcd\xef', 4)
]);
t | len | RPAD |
---|---|---|
b"abc" | 5 | b"abc " |
b"abc" | 2 | b"ab" |
b"\xab\xcd\xef" | 4 | b"\xab\xcd\xef " |
SELECT
FORMAT("%T", t) AS t,
len,
FORMAT("%T", pattern) AS pattern,
FORMAT("%T", RPAD(t, len, pattern)) AS RPAD
FROM UNNEST([
STRUCT(b'abc' AS t, 8 AS len, b'def' AS pattern),
(b'abc', 5, b'-'),
(b'\xab\xcd\xef', 5, b'\x00')
]);
t | len | padrão | RPAD |
---|---|---|---|
b"abc" | 8 | b"def" | b"abcdefde" |
b"abc" | 5 | b"-" | b"abc--" |
b"\xab\xcd\xef" | 5 | b"\x00" | b"\xab\xcd\xef\x00\x00" |
RTRIM
RTRIM(value1[, value2])
Descrição
Idêntico a TRIM, mas remove apenas os caracteres à direita.
Tipo de retorno
STRING ou BYTES
Exemplos
WITH items AS
(SELECT "***apple***" as item
UNION ALL
SELECT "***banana***" as item
UNION ALL
SELECT "***orange***" as item)
SELECT
RTRIM(item, "*") as example
FROM items;
+-----------+
| example |
+-----------+
| ***apple |
| ***banana |
| ***orange |
+-----------+
WITH items AS
(SELECT "applexxx" as item
UNION ALL
SELECT "bananayyy" as item
UNION ALL
SELECT "orangezzz" as item
UNION ALL
SELECT "pearxyz" as item)
SELECT
RTRIM(item, "xyz") as example
FROM items;
+---------+
| example |
+---------+
| apple |
| banana |
| orange |
| pear |
+---------+
SAFE_CONVERT_BYTES_TO_STRING
SAFE_CONVERT_BYTES_TO_STRING(value)
Descrição
Converte uma sequência de bytes para uma string. Qualquer caractere UTF-8 inválido será substituído pelo caractere unicode U+FFFD
.
Tipo de retorno
STRING
Exemplos
A declaração a seguir retorna o caractere de substituição Unicode �.
SELECT SAFE_CONVERT_BYTES_TO_STRING(b'\xc2') as safe_convert;
SPLIT
SPLIT(value[, delimiter])
Descrição
Divide value
usando o argumento delimiter
.
Para STRING, o delimitador padrão é a vírgula ,
.
Para BYTES, especifique um delimitador.
A divisão em um delimitador vazio produz uma matriz de caracteres UTF-8 para valores STRING, e uma matriz de BYTES para valores BYTES.
A divisão em uma STRING vazia retorna uma ARRAY com uma única STRING vazia.
Tipo de retorno
ARRAY do tipo STRING ou ARRAY do tipo BYTES
Exemplos
WITH letters AS
(SELECT "a b c d" as letter_group
UNION ALL
SELECT "e f g h" as letter_group
UNION ALL
SELECT "i j k l" as letter_group)
SELECT SPLIT(letter_group, " ") as example
FROM letters;
+----------------------+
| example |
+----------------------+
| [a, b, c, d] |
| [e, f, g, h] |
| [i, j, k, l] |
+----------------------+
STARTS_WITH
STARTS_WITH(value1, value2)
Descrição
Toma dois valores. Retorna TRUE se o segundo valor for um prefixo do primeiro.
Tipo de retorno
BOOL
Exemplos
WITH items AS
(SELECT "foo" as item
UNION ALL
SELECT "bar" as item
UNION ALL
SELECT "baz" as item)
SELECT
STARTS_WITH(item, "b") as example
FROM items;
+---------+
| example |
+---------+
| False |
| True |
| True |
+---------+
STRPOS
STRPOS(string, substring)
Descrição
Retorna o índice baseado em 1 da primeira ocorrência de substring
em string
. Retorna 0
se a substring
não for encontrada.
Tipo de retorno
INT64
Exemplos
WITH email_addresses AS
(SELECT
"foo@example.com" AS email_address
UNION ALL
SELECT
"foobar@example.com" AS email_address
UNION ALL
SELECT
"foobarbaz@example.com" AS email_address
UNION ALL
SELECT
"quxexample.com" AS email_address)
SELECT
STRPOS(email_address, "@") AS example
FROM email_addresses;
+---------+
| example |
+---------+
| 4 |
| 7 |
| 10 |
| 0 |
+---------+
SUBSTR
SUBSTR(value, position[, length])
Descrição
Retorna uma substring do valor fornecido. O argumento position
é um inteiro. Ele especifica a posição inicial da substring, com a posição = 1 indicando o primeiro caractere ou byte. O argumento length
é o número máximo de caracteres de STRING ou de bytes para argumentos BYTES.
Se position
for negativo, a função fará a contagem do final de value
, com -1 indicando o último caractere.
Se position
for uma posição à esquerda de STRING (position
= 0 ou position
< -LENGTH(value)
), a função começará da posição = 1. Se length
exceder o comprimento de value
, retornará menos que os caracteres length
.
Se length
for inferior a 0, a função retornará um erro.
Tipo de retorno
STRING ou BYTES
Exemplos
WITH items AS
(SELECT "apple" as item
UNION ALL
SELECT "banana" as item
UNION ALL
SELECT "orange" as item)
SELECT
SUBSTR(item, 2) as example
FROM items;
+---------+
| example |
+---------+
| pple |
| anana |
| range |
+---------+
WITH items AS
(SELECT "apple" as item
UNION ALL
SELECT "banana" as item
UNION ALL
SELECT "orange" as item)
SELECT
SUBSTR(item, 2, 2) as example
FROM items;
+---------+
| example |
+---------+
| pp |
| an |
| ra |
+---------+
WITH items AS
(SELECT "apple" as item
UNION ALL
SELECT "banana" as item
UNION ALL
SELECT "orange" as item)
SELECT
SUBSTR(item, -2) as example
FROM items;
+---------+
| example |
+---------+
| le |
| na |
| ge |
+---------+
TO_BASE32
TO_BASE32(bytes_expr)
Descrição
Converte uma sequência de BYTES em uma STRING codificada como base32. Para converter essa STRING em BYTES, use FROM_BASE32.
Tipo de retorno
STRING
Exemplo
SELECT TO_BASE32(b'abcde\xFF') AS base32_string;
+------------------+
| base32_string |
+------------------+
| MFRGGZDF74====== |
+------------------+
TO_BASE64
TO_BASE64(bytes_expr)
Descrição
Converte uma sequência de BYTES para uma STRING codificada como base64. Para converter essa STRING para BYTES, use FROM_BASE64.
Tipo de retorno
STRING
Exemplo
SELECT TO_BASE64(b'\xde\xad\xbe\xef') AS base64_string;
+---------------+
| base64_string |
+---------------+
| 3q2+7w== |
+---------------+
TO_CODE_POINTS
TO_CODE_POINTS(value)
Descrição
Assume um valor e retorna uma matriz de INT64.
- Se
value
for uma STRING, cada elemento na matriz retornada representará um ponto de código (em inglês). Cada ponto de código está dentro do intervalo de [0, 0xD7FF] e [0xE000, 0x10FFFF]. - Se
value
for BYTES, cada elemento na matriz será um valor de caractere ASCII estendido no intervalo de [0, 255].
Para converter uma matriz de pontos de código em STRING ou BYTES, consulte CODE_POINTS_TO_STRING ou CODE_POINTS_TO_BYTES.
Tipo de retorno
ARRAY de INT64
Exemplos
O exemplo a seguir mostra os pontos de código de cada elemento em uma matriz de palavras.
SELECT word, TO_CODE_POINTS(word) AS code_points
FROM UNNEST(['foo', 'bar', 'baz', 'giraffe', 'llama']) AS word;
+---------+------------------------------------+
| word | code_points |
+---------+------------------------------------+
| foo | [102, 111, 111] |
| bar | [98, 97, 114] |
| baz | [98, 97, 122] |
| giraffe | [103, 105, 114, 97, 102, 102, 101] |
| llama | [108, 108, 97, 109, 97] |
+---------+------------------------------------+
O exemplo a seguir converte as representações de inteiros de BYTES para os valores de caracteres ASCII correspondentes.
SELECT word, TO_CODE_POINTS(word) AS bytes_value_as_integer
FROM UNNEST([b'\x00\x01\x10\xff', b'\x66\x6f\x6f']) AS word;
+------------------+------------------------+
| word | bytes_value_as_integer |
+------------------+------------------------+
| \x00\x01\x10\xff | [0, 1, 16, 255] |
| foo | [102, 111, 111] |
+------------------+------------------------+
O exemplo a seguir demonstra a diferença entre os resultados BYTES e STRING.
SELECT TO_CODE_POINTS(b'Ā') AS b_result, TO_CODE_POINTS('Ā') AS s_result;
+------------+----------+
| b_result | s_result |
+------------+----------+
| [196, 128] | [256] |
+------------+----------+
O caractere Ā é representado como uma sequência Unicode de dois bytes. Como resultado, a versão BYTES de TO_CODE_POINTS
retorna uma matriz com dois elementos, enquanto a versão STRING retorna uma matriz com um único elemento.
TO_HEX
TO_HEX(bytes)
Descrição
Converte uma sequência de BYTES em uma STRING hexadecimal. Converte cada byte na STRING como dois caracteres hexadecimais no intervalo (0..9, a..f)
. Para converter uma STRING codificada em hexadecimal em BYTES, use FROM_HEX.
Tipo de retorno
STRING
Exemplo
WITH Input AS (
SELECT b'\x00\x01\x02\x03\xAA\xEE\xEF\xFF' AS byte_str UNION ALL
SELECT b'foobar'
)
SELECT byte_str, TO_HEX(byte_str) AS hex_str
FROM Input;
+----------------------------------+------------------+
| byte_string | hex_string |
+----------------------------------+------------------+
| foobar | 666f6f626172 |
| \x00\x01\x02\x03\xaa\xee\xef\xff | 00010203aaeeefff |
+----------------------------------+------------------+
TRIM
TRIM(value1[, value2])
Descrição
Remove todos os caracteres à direita e à esquerda que correspondem a value2
. Se value2
não for especificado, todos os espaços em branco à esquerda e à direita, conforme definido pelo padrão Unicode, serão removidos. Se o primeiro argumento for do tipo BYTES, o segundo argumento será necessário.
Se value2
contiver mais de um caractere ou byte, a função removerá todos os que estiverem à direita ou à esquerda contidos em value2
.
Tipo de retorno
STRING ou BYTES
Exemplos
WITH items AS
(SELECT " apple " as item
UNION ALL
SELECT " banana " as item
UNION ALL
SELECT " orange " as item)
SELECT
CONCAT("#", TRIM(item), "#") as example
FROM items;
+----------+
| example |
+----------+
| #apple# |
| #banana# |
| #orange# |
+----------+
WITH items AS
(SELECT "***apple***" as item
UNION ALL
SELECT "***banana***" as item
UNION ALL
SELECT "***orange***" as item)
SELECT
TRIM(item, "*") as example
FROM items;
+---------+
| example |
+---------+
| apple |
| banana |
| orange |
+---------+
WITH items AS
(SELECT "xxxapplexxx" as item
UNION ALL
SELECT "yyybananayyy" as item
UNION ALL
SELECT "zzzorangezzz" as item
UNION ALL
SELECT "xyzpearxyz" as item)
SELECT
TRIM(item, "xyz") as example
FROM items;
+---------+
| example |
+---------+
| apple |
| banana |
| orange |
| pear |
+---------+
UPPER
UPPER(value)
Descrição
Para argumentos STRING, retorna a string original com todos os caracteres alfabéticos em maiúsculas. O mapeamento entre maiúsculas e minúsculas é feito de acordo com o banco de dados de caracteres Unicode. Os mapeamentos específicos do idioma não são considerados.
Para argumentos BYTES, o argumento é tratado como texto ASCII, com todos os bytes superiores a 127 intactos.
Tipo de retorno
STRING ou BYTES
Exemplos
WITH items AS
(SELECT
"foo" as item
UNION ALL
SELECT
"bar" as item
UNION ALL
SELECT
"baz" as item)
SELECT
UPPER(item) AS example
FROM items;
+---------+
| example |
+---------+
| FOO |
| BAR |
| BAZ |
+---------+