Hologres:Funções de string

Concatenação, divisão e preenchimento de strings

Outras funções de string

STRING \|\| STRING

Concatena duas strings.

Sintaxe

<string_value1> TEXT || <string_value2> TEXT

Parâmetros

Tipo de retorno: TEXT

Exemplo

-- Returns: HologreSQL
SELECT 'Holo' || 'greSQL';

CONCAT

Concatena duas ou mais strings em uma só. Ignora argumentos NULL.

Sintaxe

CONCAT(<str1> TEXT, <str2> TEXT, ..., <strN> TEXT)

Parâmetros

Tipo de retorno: TEXT

Exemplo

SELECT CONCAT('abcde', 2, NULL, 22);

Resultado:

concat
--------
abcde222

CONCAT_WS

Concatena todos os argumentos após o primeiro, usando o primeiro argumento como separador.

Sintaxe

CONCAT_WS(<sep> TEXT, str "any" [, str "any" [, ...]])

O tipo any indica que todos os tipos de dados são aceitos.

Parâmetros

Obrigatórios:

sep: O separador a inserir entre cada string. Se sep for NULL, a função retornará NULL.

Opcionais:

str: As strings a concatenar. Argumentos de string NULL são ignorados.

Tipo de retorno: TEXT

Exemplo

SELECT CONCAT_WS(',', 'abcde', 2, NULL, 22);

Resultado:

concat_ws
----------
abcde,2,22

LISTAGG

Agrega valores da mesma coluna em várias linhas em uma única string. Esta é uma função compatível com Oracle que requer a extensão orafce. Para instruções de configuração, consulte Funções compatíveis com Oracle.

Sintaxe

LISTAGG(<str1> TEXT [, <str2> TEXT])

Parâmetros

Tipo de retorno: TEXT

Exemplos

-- Returns: hologres
SELECT LISTAGG(t) FROM (VALUES('holo'), ('gres')) AS l(t);

-- Returns: holo.gres
SELECT LISTAGG(t, '.') FROM (VALUES('holo'), ('gres')) AS l(t);

STRING_AGG

Agrega valores da mesma coluna em várias linhas em uma única string.

Sintaxe

STRING_AGG(expression [order_by_clause]) [FILTER (WHERE filter_clause)]

A cláusula FILTER é compatível com o Hologres V1.3 e versões posteriores. Para atualizar sua instância, consulte Atualizar uma instância . Para obter suporte, participe do grupo DingTalk do Hologres. Para instruções, consulte Como obtenho mais suporte online? .

Parâmetros

Tipo de retorno: TEXT

Exemplos

Exemplo 1: Agregar todos os nomes de cidades em uma única string.

CREATE TABLE city_test (
    country text,
    city text
);

INSERT INTO city_test
    VALUES ('China', 'Shanghai'), ('China', 'Taiwan'), ('Japan', 'Tokyo'), ('France', 'Paris'), ('UK', 'London');

SELECT STRING_AGG(city, ',') FROM city_test;

Resultado:

string_agg
-------------------------------
Shanghai,Taiwan,Tokyo,Paris,London

Exemplo 2: Agregar nomes onde student_id > 2.

CREATE TABLE name_text (
    student_id int,
    name text
);

INSERT INTO name_text
    VALUES (1, 'Zhang San'), (2, 'Li Si'), (3, 'Wang Wu'), (4, 'Zhou Liu');

SELECT STRING_AGG(name, ',') FILTER (WHERE student_id > 2) FROM name_text;

Resultado:

string_agg
-----------
Wang Wu,Zhou Liu

LEFT

Extrai um número especificado de caracteres do início de uma string.

Sintaxe

LEFT(<str> TEXT, <num> INT)

Parâmetros

Tipo de retorno: TEXT

Exemplo

SELECT LEFT('hologres', 4);

Resultado:

left
----
holo

RIGHT

Extrai um número especificado de caracteres do final de uma string.

Sintaxe

RIGHT(<str> TEXT, <num> INT)

Parâmetros

Tipo de retorno: TEXT

Exemplo

SELECT RIGHT('hologres', 4);

Resultado:

right
-----
gres

SPLIT_PART

Divide uma string por um delimitador e retorna o n-ésimo segmento.

Sintaxe

SPLIT_PART(<str> TEXT, <delimiter> TEXT, <num> INT)

Parâmetros

Tipo de retorno: TEXT

Notas de uso

• Se num for maior que o número de segmentos, a função retornará NULL.

Exemplo

CREATE TABLE split_part_test (
    a text
);

INSERT INTO split_part_test VALUES ('a/b/c/d/e'), ('a1/b1/c1/d1/e1');

SELECT split_part(a, '/', 2) FROM split_part_test;

Resultado:

split_part
----------
b
b1

SUBSTRING

Extrai uma substring de uma string de destino. Suporta três formas: baseada em posição, expressão regular POSIX e expressão regular SQL.

Sintaxe

Baseada em posição:

SUBSTRING(<str> TEXT [FROM <num_start> INT] [FOR <num_end> INT])

Expressão regular POSIX:

SUBSTRING(<str> TEXT FROM <pattern> TEXT)

Expressão regular SQL:

SUBSTRING(<str> TEXT FROM <pattern> TEXT FOR <escape> TEXT)

Parâmetros

Tipo de retorno: TEXT

Exemplos

Exemplo 1: Extrair uma substring por posição.

SELECT SUBSTRING('Thomas' FROM 2 FOR 3);

Resultado:

substring
---------
hom

Exemplo 2: Extrair uma substring correspondente a uma expressão regular POSIX.

SELECT SUBSTRING('Thomas' FROM '...$');

Resultado:

substring
---------
mas

Exemplo 3: Extrair uma substring correspondente a uma expressão regular SQL.

SELECT SUBSTRING('Thomas' FROM '%#"o_a#"_' FOR '#');

Resultado:

substring
---------
oma

SUBSTR

Extrai uma substring a partir de uma posição inicial. Esta é uma função compatível com Oracle que requer a extensão orafce. Para instruções de configuração, consulte Funções compatíveis com Oracle.

Sintaxe

Extrair de uma posição inicial até o final:

SUBSTR(<str> TEXT, <num_start> INTEGER)

Extrair uma substring de comprimento fixo:

SUBSTR(<str> TEXT, <num_start> INTEGER, <len> INTEGER)

Parâmetros

Tipo de retorno: TEXT

Exemplos

Exemplo 1: Extrair do quarto caractere até o final.

-- Returns: ogres
SELECT SUBSTR('Hologres', 4);

Exemplo 2: Extrair cinco caracteres começando pelo segundo caractere.

-- Returns: ologr
SELECT SUBSTR('Hologres', 2, 5);

REGEXP_MATCH

Compara uma string com uma expressão regular e retorna as correspondências como um array.

Sintaxe

REGEXP_MATCH(<str> TEXT, <pattern> TEXT)

Parâmetros

Tipo de retorno: ARRAY

Exemplo

SELECT regexp_match('foobarbequebaz', '(bar)(beque)');

Resultado:

regexp_match
------------
{bar,beque}

REGEXP_SPLIT_TO_TABLE

Divide uma string usando uma expressão regular e retorna cada segmento como uma linha separada. Use esta função para expandir valores delimitados em linhas.

Sintaxe

REGEXP_SPLIT_TO_TABLE(<str> TEXT, <pattern> TEXT)

Parâmetros

Tipo de retorno: TEXT (uma linha por segmento)

Exemplo

CREATE TABLE interests_test (
    name text,
    intrests text
);

INSERT INTO interests_test
    VALUES ('Zhang San', 'singing,dancing'), ('Li Si', 'soccer,running,drawing'), ('Wang Wu', 'flower arranging,calligraphy,playing piano,sleeping');

SELECT name, REGEXP_SPLIT_TO_TABLE(intrests, ',') FROM interests_test;

Resultado:

name      | regexp_split_to_table
----------+----------------------
Zhang San | singing
Zhang San | dancing
Li Si     | soccer
Li Si     | running
Li Si     | drawing
Wang Wu   | flower arranging
Wang Wu   | calligraphy
Wang Wu   | playing piano
Wang Wu   | sleeping

REGEXP_SPLIT_TO_ARRAY

Divide uma string usando uma expressão regular e retorna os segmentos como um array.

Sintaxe

REGEXP_SPLIT_TO_ARRAY(<str> TEXT, <pattern> TEXT)

Parâmetros

Tipo de retorno: ARRAY

Exemplo

CREATE TABLE interests_test (
    name text,
    intrests text
);

INSERT INTO interests_test
    VALUES ('Zhang San', 'singing,dancing'), ('Li Si', 'soccer,running,drawing'), ('Wang Wu', 'flower arranging,calligraphy,playing piano,sleeping');

SELECT name, REGEXP_SPLIT_TO_ARRAY(intrests, ',') FROM interests_test;

Resultado:

name      | regexp_split_to_array
----------+----------------------------------------------------------
Zhang San | {singing,dancing}
Li Si     | {soccer,running,drawing}
Wang Wu   | {"flower arranging",calligraphy,"playing piano",sleeping}

TRIM

Remove caracteres especificados de uma ou ambas as extremidades de uma string.

Sintaxe

TRIM([LEADING | TRAILING | BOTH] [<characters> TEXT] FROM <string> TEXT)

Parâmetros

Tipo de retorno: TEXT

Exemplo

-- Returns: Tom
SELECT TRIM(BOTH 'xyz' FROM 'yxTomxx');

BTRIM

Remove caracteres especificados de ambas as extremidades de uma string.

Sintaxe

BTRIM(<str> TEXT [, <characters> TEXT])

Parâmetros

Tipo de retorno: TEXT

Exemplo

-- Returns: trim
SELECT BTRIM('xyxtrimyyx', 'xyz');

LTRIM

Remove caracteres especificados da esquerda (início) de uma string.

Sintaxe

LTRIM(<str> TEXT [, <characters> TEXT])

Parâmetros

Tipo de retorno: TEXT

Exemplo

-- Returns: test
SELECT LTRIM('zzzytest', 'xyz');

RTRIM

Remove caracteres especificados da direita (final) de uma string.

Sintaxe

RTRIM(<str> TEXT [, <characters> TEXT])

Parâmetros

Tipo de retorno: TEXT

Exemplo

-- Returns: test
SELECT RTRIM('testxxzx', 'xyz');

LPAD

Preenche uma string à esquerda com um caractere de preenchimento até um comprimento total especificado.

Sintaxe

LPAD(<str> TEXT, <length> INT [, <fill> TEXT])

Parâmetros

Tipo de retorno: TEXT

Exemplo

-- Returns: xyxhi
SELECT LPAD('hi', 5, 'xy');

RPAD

Preenche uma string à direita com um caractere de preenchimento até um comprimento total especificado.

Sintaxe

RPAD(<str> TEXT, <length> INT [, <fill> TEXT])

Parâmetros

Tipo de retorno: TEXT

Exemplo

-- Returns: hixyx
SELECT RPAD('hi', 5, 'xy');

ASCII

Retorna o código ASCII do primeiro caractere de uma string.

Sintaxe

ASCII(<str> TEXT)

Tipo de retorno: INTEGER

Exemplo

-- Returns: 120
SELECT ASCII('x');

CHAR_LENGTH

Retorna o número de caracteres em uma string. Equivalente a LENGTH.

Sintaxe

CHAR_LENGTH(<str> TEXT)

Tipo de retorno: INTEGER

Exemplo

-- Returns: 4
SELECT CHAR_LENGTH('jose');

LENGTH

Retorna o número de caracteres em uma string. Na codificação UTF-8, cada caractere chinês, dígito ou letra conta como um caractere. Equivalente a CHAR_LENGTH.

Sintaxe

LENGTH(<str> TEXT)

Tipo de retorno: INTEGER

Exemplo

-- Returns: 4
SELECT LENGTH('jose');

CHR

Retorna o caractere correspondente a um determinado valor de código ASCII ou Unicode.

Sintaxe

CHR(<num> INT)

Tipo de retorno: TEXT

Exemplo

-- Returns: A
SELECT CHR(65);

INITCAP

Converte a primeira letra de cada palavra para maiúscula e o restante para minúsculas. Palavras são sequências de caracteres alfanuméricos separados por caracteres não alfanuméricos.

Sintaxe

INITCAP(<str> TEXT)

Tipo de retorno: TEXT

Exemplo

-- Returns: Hi Thomas
SELECT INITCAP('hi THOMAS');

LOWER

Converte uma string para minúsculas.

Sintaxe

LOWER(<str> TEXT)

Tipo de retorno: TEXT

Exemplo

-- Returns: tom
SELECT LOWER('TOM');

UPPER

Converte uma string para maiúsculas.

Sintaxe

UPPER(<str> TEXT)

Tipo de retorno: TEXT

Exemplo

-- Returns: TOM
SELECT UPPER('tom');

OCTET_LENGTH

Retorna o número de bytes em uma string.

Sintaxe

OCTET_LENGTH(<str> TEXT)

Tipo de retorno: INTEGER

Exemplo

-- Returns: 4
SELECT OCTET_LENGTH('jose');

MD5

Calcula o hash MD5 de uma string e retorna o resultado como uma string hexadecimal.

Sintaxe

MD5(<str> TEXT)

Tipo de retorno: TEXT

Exemplo

-- Returns: 900150983cd24fb0d6963f7d28e17f72
SELECT MD5('abc');

POSITION

Retorna a posição inicial de uma substring dentro de uma string. Retorna 0 se a substring não for encontrada. Equivalente a STRPOS.

Sintaxe

POSITION(<substr> TEXT IN <str> TEXT)

Parâmetros

Tipo de retorno: INTEGER

Exemplo

-- Returns: 3
SELECT POSITION('om' IN 'Thomas');

STRPOS

Retorna a posição inicial de uma substring dentro de uma string. Retorna 0 se a substring não for encontrada. Equivalente a POSITION.

Sintaxe

STRPOS(<str> TEXT, <substr> TEXT)

Parâmetros

Tipo de retorno: INTEGER

Exemplo

-- Returns: 2
SELECT STRPOS('high', 'ig');

INSTR

Retorna a posição de uma substring dentro de uma string de destino, com controle opcional de posição inicial e ocorrência. Retorna 0 se a substring não for encontrada. Esta é uma função compatível com Oracle que requer a extensão orafce. Para instruções de configuração, consulte Funções compatíveis com Oracle.

Sintaxe

INSTR(<str> TEXT, <patt> TEXT [, <start> INTEGER [, <nth> INTEGER]])

Parâmetros

Tipo de retorno: INTEGER

Exemplos

-- Returns: 4 (second occurrence of 'o', searching from position 1)
SELECT INSTR('Hologres', 'o', 1, 2);

-- Returns: 2 (first occurrence of 'o', searching from position 1)
SELECT INSTR('Hologres', 'o', 1, 1);

-- Returns: 4 (first occurrence of 'o' at or after position 4)
SELECT INSTR('Hologres', 'o', 4);

-- Returns: 2 (first occurrence of 'o')
SELECT INSTR('Hologres', 'o');

REPLACE

Substitui todas as ocorrências de uma substring dentro de uma string.

Sintaxe

REPLACE(<str> TEXT, <old_str> TEXT, <new_str> TEXT)

Parâmetros

Tipo de retorno: TEXT

Exemplo

CREATE TABLE animal_test (
    animal text,
    color text
);

INSERT INTO animal_test
    VALUES ('dog', 'white'), ('cat', 'white'), ('tiger', 'yellow');

SELECT animal, REPLACE(color, 'white', 'multi-colored') FROM animal_test;

Resultado:

animal | replace
-------+--------------
dog    | multi-colored
cat    | multi-colored
tiger  | yellow

REGEXP_REPLACE

Substitui substrings correspondentes a uma expressão regular POSIX por uma string de substituição.

Sintaxe

REGEXP_REPLACE(<str> TEXT, <regex> TEXT, <replacement> TEXT [, <flags> TEXT])

Parâmetros

Obrigatórios:

Opcionais:

flags: Um ou mais caracteres que controlam o comportamento de correspondência. Padrão: substitui apenas a primeira correspondência, diferencia maiúsculas de minúsculas.

Tipo de retorno: TEXT

Exemplo

CREATE TABLE a_test (
    a text
);

INSERT INTO a_test VALUES ('Abcd1234abCd');

SELECT REGEXP_REPLACE(a, '1234.', '77', 'ig') FROM a_test;

Resultado:

regexp_replace
--------------
Abcd77bCd

TRANSLATE

Substitui caracteres individuais em uma string com base em um mapeamento de caracteres.

Sintaxe

TRANSLATE(<str> TEXT, <substr> TEXT, <newstr> TEXT)

Parâmetros

Tipo de retorno: TEXT

Exemplo

-- Returns: a2x5
SELECT TRANSLATE('12345', '143', 'ax');

REPEAT

Repete uma string um número especificado de vezes.

Sintaxe

REPEAT(<str> TEXT, <number> INT)

Parâmetros

Tipo de retorno: TEXT

Exemplo

-- Returns: PgPgPgPg
SELECT REPEAT('Pg', 4);

STARTS_WITH

Retorna verdadeiro se uma string começar com um prefixo especificado.

Sintaxe

STARTS_WITH(<str> TEXT, <prefix> TEXT)

Parâmetros

Tipo de retorno: BOOLEAN (t para verdadeiro, f para falso)

Exemplo

-- Returns: t
SELECT STARTS_WITH('alphabet', 'alph');

PARSE_IDENT

Analisa uma string de identificador SQL qualificado e retorna seus componentes como um array. Componentes sem aspas são convertidos para minúsculas; componentes com aspas preservam o caso original.

Sintaxe

PARSE_IDENT(<quali_iden> TEXT)

Parâmetros

Tipo de retorno: TEXT (formato de array)

Exemplo

-- Returns: {"SomeSchema","sometable"}
SELECT PARSE_IDENT('"SomeSchema".someTable');

QUOTE_IDENT

Retorna a string entre aspas como um identificador SQL válido, adicionando aspas duplas quando necessário (por exemplo, quando a string contém espaços ou letras maiúsculas que, de outra forma, seriam convertidas para minúsculas).

Sintaxe

QUOTE_IDENT(<str> TEXT)

Tipo de retorno: TEXT

Exemplo

-- Returns: "Foo bar"
SELECT QUOTE_IDENT('Foo bar');

QUOTE_LITERAL

Retorna a string entre aspas como uma constante de string SQL válida, escapando aspas simples conforme necessário.

Sintaxe

QUOTE_LITERAL(<str> TEXT)

Tipo de retorno: TEXT

Exemplo

-- Returns: 'O''Reilly'
SELECT QUOTE_LITERAL(E'O\'Reilly');

TO_HEX

Converte um inteiro em sua representação de string hexadecimal.

Sintaxe

TO_HEX(<number> INT | BIGINT)

Tipo de retorno: TEXT

Exemplo

-- Returns: 7fffffff
SELECT TO_HEX(2147483647);

TO_NUMBER

Converte uma string formatada em um valor numérico usando um modelo de formato.

Sintaxe

TO_NUMBER(<str1> TEXT, <str2> TEXT)

Parâmetros

Tipo de retorno: NUMERIC

Exemplo

-- Returns: -12454.8
SELECT TO_NUMBER('12,454.8-', '99G999D9S');

PLVSTR.RVRS

Inverte parte ou toda uma string. Esta é uma função compatível com Oracle que requer a extensão orafce. Para instruções de configuração, consulte Funções compatíveis com Oracle.

Sintaxe

Inverter a string inteira:

PLVSTR.RVRS(<str> TEXT)

Inverter de uma posição inicial até o final:

PLVSTR.RVRS(<str> TEXT, <start> INTEGER)

Inverter um intervalo especificado:

PLVSTR.RVRS(<str> TEXT, <start> INTEGER, <end> INTEGER)

Parâmetros

Tipo de retorno: TEXT

Exemplos

-- Returns: sergoloH (entire string reversed)
SELECT PLVSTR.RVRS('Hologres');

-- Returns: sergo (reversed from position 4 to end)
SELECT PLVSTR.RVRS('Hologres', 4);

-- Returns: rg (positions 5-6 reversed)
SELECT PLVSTR.RVRS('Hologres', 5, 6);

ROW

Retorna o número da linha atual. Compatível com o Hologres V1.3 e versões posteriores.

Para atualizar sua instância para a versão V1.3 ou posterior, consulte Atualizar uma instância . Para obter suporte, participe do grupo DingTalk do Hologres. Para instruções, consulte Como obtenho mais suporte online? .

Sintaxe

ROW(<val1>, <val2>, ...)

Tipo de retorno: ARRAY ou TEXT

Exemplo

O exemplo a seguir retorna linhas onde a coluna a e a coluna b possuem valores iguais.

CREATE TABLE row_test (
    a text,
    b text
);

INSERT INTO row_test VALUES ('0', '0'), ('1', '0');

SELECT a, b FROM row_test WHERE ROW(a, b) = ROW(b, a);

Resultado:

a | b
--+--
0 | 0