CHARACTER HANDLING

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
  

Poston Wed, 10 Oct 2007 9:49 am

Staff is as follows: I have a column that exists in the the following records: NUM_NOTA----------------------15-234-9111-IRRF 51-124-3407-IRRF IRRF 30-1107-IRRF ...


I need to get only the Middle numbers, I could do using the function \"SUBSTR\" but the numbers always vary in size. I did it once but I do not remember more, can anyone help me please?
cleberzumba
Location: Brasília-DF

Cleber Zumba de Souza

Poston Wed, 10 Oct 2007 10:12 am

I have the following function that extracts a particular \"element\" of a string (with a separator character).

Code: Select all
/*
PARAMETROS DE ENTRADA
- p_string : varchar2 contendo o string completo com os n elementos.
- p_element: pls_integer contendo o índice do elemento a ser extraído.
- p_separador: varchar2 contendo a marca utilizada como separadora dos elementos.

RETORNO
- Varchar2 com o elemento solicitado por parâmetro. Se não existir, retorna NULL
*/
FUNCTION elemento(p_string VARCHAR2, p_elemento PLS_INTEGER, p_separador VARCHAR2 DEFAULT cte_SEPARADOR) RETURN VARCHAR2 AS
  v_string VARCHAR2(5000);
BEGIN
  v_string := p_string || p_separador;
  FOR i IN 1 .. p_elemento - 1
  LOOP
    v_string := SUBSTR(v_string,INSTR(v_string,p_separador)+LENGTH(p_separador));
  END LOOP;
  RETURN SUBSTR(v_string,1,INSTR(v_string,p_separador)-1);
END elemento;   
type: If your vstring is ' 10; 20; 30; 40; 50 ', and ask:
Code: Select all
elemento( vstring, '3', ';')
He returns 30.

Maybe you can implement it in your case. :-o
dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered

Poston Wed, 10 Oct 2007 11:23 am

Thanks, served ...It was very useful, thanks a lot.
cleberzumba
Location: Brasília-DF

Cleber Zumba de Souza



Return to SQL

Who is online

Users browsing this forum: No registered users and 3 guests