Conceito
Função que divide a lista passada no parâmetro string usando como delimitador o parâmetro delimiter, e retorna somente o item da lista informado no parâmetro field.
Esta função é nativa em PostgreSQL e foi implementada em Oracle exatamente com a mesma funcionalidade.
Em Oracle o arquivo de criação desta função é o totalicommerce\VERSAO\ansi\function\0ora\SPLITPART.sql.
Assinatura de Split_Part
Oracle
Split_Part(string VARCHAR2, delimiter VARCHAR2, field NUMBER) return VARCHAR2
PostgreSQL
Split_Part(string TEXT, delimiter TEXT, field INT) return TEXT;
Parâmetros
string: (VARCHAR2 em Oracle, TEXT em PostgreSQL)
Lista de valores com os campos delimitados.
Ex.: 'San Francisco;Los Angeles;London;Monaco'
delimiter: (VARCHAR2 em Oracle, TEXT em PostgreSQL)
Delimitador dos campos da lista.
Recomenda-se usar o ponto e vírgula (😉 como delimitador padrão.
Ex.: ';'
field: (NUMBER em Oracle, INT em PostgreSQL)
Número do campo que será retornado. O primeiro campo da lista é o 1 (um).
Caso seja informado um valor menor que 1 para este parâmetro, uma exceção é levantada no banco de dados.
Ex.: 3
Retorno
return: (VARCHAR2 em Oracle, TEXT em PostgreSQL)
A função retorna o campo solicitado como uma string (VARCHAR2 em Oracle, TEXT em PostgreSQL).
Caso não seja encontrado o valor informado no parâmetro delimiter, é retornada uma string vazia para o PostgreSQL e um nulo (null) em Oracle.
Caso seja informado um valor maior que o último item da lista para o parâmetro field, é retornada uma string vazia para o PostgreSQL e um nulo (null) em Oracle.
Caso seja informado um valor menor que 1 para o parâmetro field, uma exceção é levantada no banco de dados.
Veja os exemplos abaixo.
Exemplos de Uso
SELECT SPLIT_PART('San Francisco;Los Angeles;London;Monaco', ';', 3) FROM dual; Resultado: 'London'
SELECT SPLIT_PART('San Francisco;Los Angeles;London;Monaco', ';', 1) FROM dual; Resultado: 'San Francisco'
-- delimiter não encontrado SELECT SPLIT_PART('San Francisco;Los Angeles;London;Monaco', '|', 3) FROM dual; Resultado em Oracle: null Resultado em PostgreSQL:
-- item maior que a quantidade de itens da lista SELECT SPLIT_PART('San Francisco;Los Angeles;London;Monaco', ';', 6) FROM dual; Resultado em Oracle: null Resultado em PostgreSQL:
-- item menor que 1 (um) SELECT SPLIT_PART('San Francisco;Los Angeles;London;Monaco', ';', 0) FROM dual; Resultado em Oracle: Erro de SQL: ORA-20001: O parâmetro "item" da função "split_part" deve receber um valor maior ou igual a 1 (um). Resultado em PostgreSQL: ERROR: field position must be greater than zero
Utilizando a lista com outros tipos de dados
Apesar da lista ser uma string, esta função tem como intuito ajudar a tratar os resultados de outras funções que retornam uma lista com vários tipos de dados.
Abaixo serão mostradas algumas definições de como o parâmetro string deve ser gerado a partir de outras funções a fim de facilitar a conversão de dados quando não forem uma string (Char, Varchar, Varchar2 e Text) nativa.
Números Inteiros
Utilizar somente os números sem separadores de milhar.
Ex.: '123' ou '2569833'
Números com Decimais
Utilizar somente os números com o separador decimal, sem separadores de milhar. Para o separador decimal utilize o padrão que as funções de conversão To_Char retornar.
Ex.: '12.34' ou '9999999,9999'
Data
Utilizar sempre o formato (DD/MM/YYYY) onde DD é o dia sempre com dois dígitos, o MM é o mês em formato numérico sempre com dois dígitos e YYYY é o ano completo com quatro dígitos.
Ex.: '01/01/2000' ou '29/09/2014'
Data e Hora
Utilizar sempre o formato (DD/MM/YYYY HH24:MI:SS) onde DD é o dia sempre com dois dígitos, o MM é o mês em formato numérico sempre com dois dígitos, o YYYY é o ano completo com quatro dígitos, o HH são as horas sempre com dois dígitos e o 24 indica que será utilizada a notação de 0 a 24 horas, o MI são os minutos sempre com dois dígitos e o SS são os segundos sempre com dois dígitos também.
Ex.: '01/01/2000 08:30:00' ou '29/09/2014 14:03:45'
Exemplo
SELECT SPLIT_PART('Nome Completo;12345;2999589,95;20/12/1978;29/09/2014 14:03:45', ';', 1) FROM dual; Resultado: 'Nome Completo'
SELECT SPLIT_PART('Nome Completo;12345;2999589,95;20/12/1978;29/09/2014 14:03:45', ';', 2) FROM dual; Resultado: '12345'
SELECT SPLIT_PART('Nome Completo;12345;2999589,95;20/12/1978;29/09/2014 14:03:45', ';', 3) FROM dual; Resultado: '2999589,95'
SELECT SPLIT_PART('Nome Completo;12345;2999589,95;20/12/1978;29/09/2014 14:03:45', ';', 4) FROM dual; Resultado: '20/12/1978'
SELECT SPLIT_PART('Nome Completo;12345;2999589,95;20/12/1978;29/09/2014 14:03:45', ';', 5) FROM dual; Resultado: '29/09/2014 14:03:45'
Função Split_Part_Null
Função que divide a lista passada no parâmetro pString usando como delimitador o parâmetro pDelimiter, e retorna somente o item da lista informado no parâmetro pField.
Esta função é uma extensão da função Split_Part e a principal diferença é que Split_Part_Null retorna nulo (null) caso o campo pField selecionado seja uma string vazia ou somente com espaços em branco.
Esta diferença é especialmente interessante quando se utiliza o resultado obtido em funções de conversão de dados, como To_Date e To_Number, pois estas funções de conversão levantarão uma exceção caso tentem converter uma string vazia, ou somente com espaços em branco, mas retornam nulo (null) caso tentem converter um nulo (null).
Todos os demais tratamentos e funcionalidades de Split_Part_Null são iguais a Split_Part.
O arquivo de criação desta função é o totalicommerce\VERSAO\ansi\function\1\SplitPartNull.mac.
Assinatura de Split_Part_Null
Oracle
Split_Part_Null(pString VARCHAR2, pDelimiter VARCHAR2, pField NUMBER) return VARCHAR2
PostgreSQL
Split_Part_Null(pString VARCHAR, pDelimiter VARCHAR, pField NUMERIC) return VARCHAR;
Parâmetros
pString: (VARCHAR2 em Oracle, VARCHAR em PostgreSQL)
Lista de valores com os campos delimitados.
Ex.: 'San Francisco;Los Angeles;London;Monaco'
pDelimiter: (VARCHAR2 em Oracle, VARCHAR em PostgreSQL)
Delimitador dos campos da lista.
Recomenda-se usar o ponto e vírgula (😉 como delimitador padrão.
Ex.: ';'
pField: (NUMBER em Oracle, NUMERIC em PostgreSQL)
Número do campo que será retornado. O primeiro campo da lista é o 1 (um).
Caso seja informado um valor menor que 1 para este parâmetro, uma exceção é levantada no banco de dados.
Ex.: 3
Retorno
return: (VARCHAR2 em Oracle, VARCHAR em PostgreSQL)
A função retorna o campo solicitado como uma string (VARCHAR2 em Oracle, VARCHAR em PostgreSQL).
Caso não seja encontrado o valor informado no parâmetro pDelimiter, é retornado nulo (null).
Caso seja informado um valor maior que o último item da lista para o parâmetro pField, é retornado nulo (null).
Caso seja informado um valor menor que 1 para o parâmetro pField, uma exceção é levantada no banco de dados.
Caso o valor a ser retornado seja uma string vazia, ou somente com espaços em branco, em seu lugar será retornado nulo (null).
Veja os exemplos abaixo.
Exemplos de Uso
SELECT SPLIT_PART_NULL('San Francisco;Los Angeles;London;; ;Monaco', ';', 3) FROM dual; Resultado: 'London'
SELECT SPLIT_PART_NULL('San Francisco;Los Angeles;London;; ;Monaco', ';', 1) FROM dual; Resultado: 'San Francisco'
SELECT SPLIT_PART_NULL('San Francisco;Los Angeles;London;; ;Monaco', ';', 4) FROM dual; Resultado: null
SELECT SPLIT_PART_NULL('San Francisco;Los Angeles;London;; ;Monaco', ';', 5) FROM dual; Resultado: null
-- delimiter não encontrado SELECT SPLIT_PART_NULL('San Francisco;Los Angeles;London;; ;Monaco', '|', 3) FROM dual; Resultado: null
-- item maior que a quantidade de itens da lista SELECT SPLIT_PART_NULL('San Francisco;Los Angeles;London;; ;Monaco', ';', 8) FROM dual; Resultado: null
-- item menor que 1 (um) SELECT SPLIT_PART_NULL('San Francisco;Los Angeles;London;; ;Monaco', ';', 0) FROM dual; Resultado em Oracle: Erro de SQL: ORA-20001: O parâmetro "item" da função "split_part" deve receber um valor maior ou igual a 1 (um). Resultado em PostgreSQL: ERROR: field position must be greater than zero
Função Split_Part_Number
Função que divide a lista passada no parâmetro pString usando como delimitador o parâmetro pDelimiter, e retorna somente o item da lista informado no parâmetro pField como um número (do tipo NUMBER em Oracle e do tipo NUMERIC em PostgreSQL).
Esta função é uma extensão da função Split_Part e a principal diferença é que Split_Part_Number retorna nulo (null) caso o campo pField selecionado seja uma string vazia ou somente com espaços em branco, e caso a string esteja preenchida a função tenta fazer a conversão para o tipo numérico, tratando os separadores de milhar e decimais, evitando assim o uso de funções de conversão como "To_Number".
Todos os demais tratamentos e funcionalidades de Split_Part_Number são iguais a Split_Part.
O arquivo de criação desta função é o totalicommerce\VERSAO\ansi\function\1\SplitPartNumber.mac.
Assinatura de Split_Part_Number
Oracle
Split_Part_Number(pString VARCHAR2, pDelimiter VARCHAR2, pField NUMBER) return NUMBER
PostgreSQL
Split_Part_Number(pString VARCHAR, pDelimiter VARCHAR, pField NUMERIC) return NUMERIC;
Parâmetros
pString: (VARCHAR2 em Oracle, VARCHAR em PostgreSQL)
Lista de valores com os campos delimitados.
Ex.: 'San Francisco;Los Angeles;London;Monaco'
pDelimiter: (VARCHAR2 em Oracle, VARCHAR em PostgreSQL)
Delimitador dos campos da lista.
Recomenda-se usar o ponto e vírgula (😉 como delimitador padrão.
Ex.: ';'
pField: (NUMBER em Oracle, NUMERIC em PostgreSQL)
Número do campo que será retornado. O primeiro campo da lista é o 1 (um).
Caso seja informado um valor menor que 1 para este parâmetro, uma exceção é levantada no banco de dados.
Ex.: 3
Retorno
return: (NUMBER em Oracle, NUMERIC em PostgreSQL)
A função retorna o campo solicitado como um número (NUMBER em Oracle, NUMERIC em PostgreSQL).
Caso não seja encontrado o valor informado no parâmetro pDelimiter, é retornado nulo (null).
Caso seja informado um valor maior que o último item da lista para o parâmetro pField, é retornado nulo (null).
Caso seja informado um valor menor que 1 para o parâmetro pField, uma exceção é levantada no banco de dados.
Caso o valor a ser retornado seja uma string vazia, ou somente com espaços em branco, em seu lugar será retornado nulo (null).
Caso o pField selecionado de pString não seja um número válido uma exceção é levantada no banco de dados informando o erro de conversão dos tipos.
Veja os exemplos abaixo.
Exemplos de Uso
select Split_Part_Number('123;-456;123.456,789;;ABC', ';', 1) as NUMERO from DUAL; Resultado: 123
select Split_Part_Number('123;-456;123.456,789;;ABC', ';', 2) as NUMERO from DUAL; Resultado: -456
select Split_Part_Number('123;-456;123.456,789;;ABC', ';', 3) as NUMERO from DUAL; Resultado em Oracle: 123456,789 -- o separador decimal é a vírgula Resultado em PostgreSQL: 123.456789 -- o separador decimal é o ponto
select Split_Part_Number('123;-456;123.456,789;;ABC', ';', 4) as NUMERO from DUAL; Resultado: null
select Split_Part_Number('123;-456;123.456,789;;ABC', ';', 5) as NUMERO from DUAL; Resultado em Oracle: Erro de SQL: ORA-06502: PL/SQL: erro numérico ou de valor Resultado em PostgreSQL: ERROR: invalid input syntax for type numeric: " "
-- pDelimiter não encontrado select Split_Part_Number('123;-456;123.456,789;;ABC', '|', 3) as NUMERO from DUAL; Resultado: null
-- pItem maior que a quantidade de itens da lista select Split_Part_Number('123;-456;123.456,789;;ABC', ';', 6) as NUMERO from DUAL; Resultado: null
-- pItem menor que 1 (um) select Split_Part_Number('123;-456;123.456,789;;ABC', ';', 0) as NUMERO from DUAL; Resultado em Oracle: Erro de SQL: ORA-20001: O parâmetro "item" da função "split_part" deve receber um valor maior ou igual a 1 (um). Resultado em PostgreSQL: ERROR: field position must be greater than zero
Alterações
Nessa seção estão relacionadas as alterações que a função sofreu a cada versão. Desta forma, pode-se por aqui verificar quais tratamentos passam a ser necessários em sistemas que utilizam a função e vão passar a utilizar uma versão mais nova.
Versão 5.1E
- Criação da função Split_Part em Oracle (compatível com a função homônima em PostgreSQL).
Versão 5.2A
- Criação da função Split_Part_Null.
- Criação da função Split_Part_Number.