[oracle_br] Re: pesquisar texto no campo
Marcio, Gostei do desafio, apesar de achar a modelagem um tanto confusa. Depois do exemplo do Chiappa (que sempre é um gatilho mais ligeiro), terminei o meu código e resolvi postar apenas para servir de complemento para você. Vamos lá: criei uma tabela XPROD, XPRODUTO e carreguei com seu exemplo, depois criei dois TYPE e uma FUNCTION de apoio. --carregando dados de teste create table xprod (seqxprod number, col1 varchar2(100), col2 varchar2(100)); insert into xprod values (1,'Tipo 1','1,2,3'); insert into xprod values (2,'Tipo 2','4,5,6,7,8,9,10,11'); insert into xprod values (3,'Tipo 3','12,13,14'); des select * from xprod; SEQXPROD COL1 COL2 -- -- 1 Tipo 1 1,2,3 2 Tipo 2 4,5,6,7,8,9,10,11 3 Tipo 3 12,13,14 des create table xproduto (codprod number, nomeprod varchar2(50)); des select * from xproduto; CODPROD NOMEPROD -- -- 2 GELADEIRA 10 MESA 12 HOME THEATER --Crie TYPE auxiliares create or replace type t_col as object (i number) / create or replace type t_nested_table as table of t_col / --Funcao para desconcatenar e retornar como TABLE create or replace function return_table (p_col2 in varchar2) return t_nested_table as v_ret t_nested_table; w_piece varchar2(50); n number; w_sql varchar2(200); begin --Referencias: -- http://www.adp-gmbh.ch/ora/plsql/coll/return_table.html -- v_ret := t_nested_table(); w_sql := p_col2; loop exit when w_sql is NULL; n := instr( w_sql, ',' ); w_piece := substr( w_sql, 1, n-1 ); -- 'pescar' a primeira parte w_sql := substr( w_sql, n+1 );-- contem o restante da string if w_piece is null then --ultima ocorrencia e nao tem mais VIRGULAS, retorna esta e termina w_piece := w_sql; w_sql := null; end if; -- v_ret.extend; v_ret(v_ret.count) := t_col(w_piece); -- carrega o vetor com a posicao pescada acima -- end loop; return v_ret; end return_table; / --procurando o tipo do produto des select a.seqxprod, b.CODPROD, b.NOMEPROD, a.col1, a.col2 agrupamento from xprod a, xproduto b where b.CODPROD in (select i valores from table(return_table(col2))) NM@nmdes / SEQXPRODCODPROD NOMEPRODCOL1 AGRUPAMENTO -- -- --- -- -- 1 2 GELADEIRA Tipo 1 1,2,3 2 10 MESATipo 2 4,5,6,7,8,9,10,11 3 12 HOME THEATERTipo 3 12,13,14 Que tal? []´s Ederson Elias DBA Oracle http://br.linkedin.com/pub/ederson-elias/24/8b/8b0 Labor improbus omnia vincit --- Em oracle_br@yahoogrupos.com.br, Grupos marcio_cbj@... escreveu Boas. Estou precisando pesquisar um texto dentro de um campo, e retornar a informação de outra coluna onde o texto encontra-se. Por exemplo, Produto COL1 COL2 Tipo 1 1,2,3 Tipo 2 4,5,6,7,8,9,10,11 O select ficaria mais ou menos assim: SELECT COL1 FROM PRODUTO WHERE COD_PRODUTO LIKE COL2 --- (WHERE 4 LIKE COL2) Tenho que trazer a informação do Tipo do produto que está na COL2, observem que os produtos foram cadastrados como uma única string, então dentro de cada string tem vários códigos. Não sei se fui claro, qualquer coisa tento explicar melhor. Oracle 10.2.0.5
Re: [oracle_br] Re: pesquisar texto no campo
Caso seja Oracle 11g, temos também a função REGEXP_INSTR:
SELECT
REGEXP_INSTR
('0123456789', -- source char or search value
'(123)(4(56)(78))', -- regular expression patterns
1, -- position to start searching
1, -- occurrence
0, -- return option
'i',-- match option (case insensitive)
1) -- subexpression on which to search
Position
FROM dual;
Exemplo:
SELECT REGEXP_INSTR('0123456789',
'(123)(4(56)(78))',
1, 1, 0, 'i',
4) Position
FROM dual;
Att.
Sérgio Chaves.
- Original Message -
From: ederson2001br ederson200...@yahoo.com.br
To: oracle br oracle_br@yahoogrupos.com.br
Sent: Terça-feira, 8 de Outubro de 2013 10:21:18
Subject: [oracle_br] Re: pesquisar texto no campo
Marcio,
Gostei do desafio, apesar de achar a modelagem um tanto confusa. Depois do
exemplo do Chiappa (que sempre é um gatilho mais ligeiro), terminei o meu
código e resolvi postar apenas para servir de complemento para você.
Vamos lá: criei uma tabela XPROD, XPRODUTO e carreguei com seu exemplo, depois
criei dois TYPE e uma FUNCTION de apoio.
--carregando dados de teste
create table xprod (seqxprod number, col1 varchar2(100), col2 varchar2(100));
insert into xprod values (1,'Tipo 1','1,2,3');
insert into xprod values (2,'Tipo 2','4,5,6,7,8,9,10,11');
insert into xprod values (3,'Tipo 3','12,13,14');
des select * from xprod;
SEQXPROD COL1 COL2
-- --
1 Tipo 1 1,2,3
2 Tipo 2 4,5,6,7,8,9,10,11
3 Tipo 3 12,13,14
des create table xproduto (codprod number, nomeprod varchar2(50));
des select * from xproduto;
CODPROD NOMEPROD
-- --
2 GELADEIRA
10 MESA
12 HOME THEATER
--Crie TYPE auxiliares
create or replace type t_col as object (i number)
/
create or replace type t_nested_table as table of t_col
/
--Funcao para desconcatenar e retornar como TABLE
create or replace function return_table (p_col2 in varchar2) return
t_nested_table as
v_ret t_nested_table;
w_piece varchar2(50);
n number;
w_sql varchar2(200);
begin
--Referencias:
-- http://www.adp-gmbh.ch/ora/plsql/coll/return_table.html
--
v_ret := t_nested_table();
w_sql := p_col2;
loop exit when w_sql is NULL;
n := instr( w_sql, ',' );
w_piece := substr( w_sql, 1, n-1 ); -- 'pescar' a primeira parte
w_sql := substr( w_sql, n+1 ); -- contem o restante da string
if w_piece is null then
--ultima ocorrencia e nao tem mais VIRGULAS, retorna esta e termina
w_piece := w_sql;
w_sql := null;
end if;
--
v_ret.extend;
v_ret(v_ret.count) := t_col(w_piece); -- carrega o vetor com a posicao
pescada acima
--
end loop;
return v_ret;
end return_table;
/
--procurando o tipo do produto
des select a.seqxprod, b.CODPROD,
b.NOMEPROD, a.col1, a.col2 agrupamento
from xprod a,
xproduto b
where b.CODPROD
in (select i valores from table(return_table(col2)))
NM@nmdes /
SEQXPROD CODPROD NOMEPROD COL1 AGRUPAMENTO
-- -- --- -- --
1 2 GELADEIRA Tipo 1 1,2,3
2 10 MESA Tipo 2 4,5,6,7,8,9,10,11
3 12 HOME THEATER Tipo 3 12,13,14
Que tal?
[]´s
Ederson Elias
DBA Oracle
http://br.linkedin.com/pub/ederson-elias/24/8b/8b0
Labor improbus omnia vincit
--- Em oracle_br@yahoogrupos.com.br, Grupos marcio_cbj@... escreveu
Boas.
Estou precisando pesquisar um texto dentro de um campo, e retornar a
informação de outra coluna onde o texto encontra-se.
Por exemplo,
Produto
COL1 COL2
Tipo 1 1,2,3
Tipo 2 4,5,6,7,8,9,10,11
O select ficaria mais ou menos assim:
SELECT COL1 FROM PRODUTO WHERE COD_PRODUTO LIKE COL2 --- (WHERE 4 LIKE
COL2)
Tenho que trazer a informação do Tipo do produto que está na COL2, observem
que os produtos foram cadastrados como uma única string, então dentro de
cada string tem vários códigos.
Não sei se fui claro, qualquer coisa tento explicar melhor.
Oracle 10.2.0.5
Re: [oracle_br] Re: pesquisar texto no campo
Além desta função há também REGEXP_COUNT, Exemplo:
CREATE OR REPLACE FUNCTION get_subexp_count
(p_subexp VARCHAR2)RETURN NUMBER
IS
v_dna CLOB;
v_countNUMBER;
BEGIN
v_dna :=
'ccacctttccctccactcctcacgttctcacctgtaaagcgtccctccctcatatgcttaccctgcag
ggtagagtaggctagaaaccagagagctccaagctccatctgtggagaggtgccatccttgggctgcagagagaggagaat
ttgaaagctgcctgcagagcttcaccacccttagtctcacaaagccttgagttcatagcatttcttgagcacc
ctgcccagcaggacactgcagcacccaaagggcttcccaggagtagggttgccctcaagaggctcttgggtctgatggcca
catcctggaattgcaagttgatggtcacagccctgaggcatgtacgtatgcgctctgctctgctctcct
ctcctgaatgaaccctctggctaagagcacttagagccag';
v_count := REGEXP_COUNT(v_dna, p_subexp);
RETURN (v_count);
END;
att.
Sérgio Chaves.
- Original Message -
From: Sérgio Luiz Rodrigues Chaves sergio.cha...@elumini.com.br
To: oracle br oracle_br@yahoogrupos.com.br
Sent: Terça-feira, 8 de Outubro de 2013 11:04:49
Subject: Re: [oracle_br] Re: pesquisar texto no campo
Caso seja Oracle 11g, temos também a função REGEXP_INSTR:
SELECT
REGEXP_INSTR
('0123456789', -- source char or search value
'(123)(4(56)(78))', -- regular expression patterns
1, -- position to start searching
1, -- occurrence
0, -- return option
'i',-- match option (case insensitive)
1) -- subexpression on which to search
Position
FROM dual;
Exemplo:
SELECT REGEXP_INSTR('0123456789',
'(123)(4(56)(78))',
1, 1, 0, 'i',
4) Position
FROM dual;
Att.
Sérgio Chaves.
- Original Message -
From: ederson2001br ederson200...@yahoo.com.br
To: oracle br oracle_br@yahoogrupos.com.br
Sent: Terça-feira, 8 de Outubro de 2013 10:21:18
Subject: [oracle_br] Re: pesquisar texto no campo
Marcio,
Gostei do desafio, apesar de achar a modelagem um tanto confusa. Depois do
exemplo do Chiappa (que sempre é um gatilho mais ligeiro), terminei o meu
código e resolvi postar apenas para servir de complemento para você.
Vamos lá: criei uma tabela XPROD, XPRODUTO e carreguei com seu exemplo, depois
criei dois TYPE e uma FUNCTION de apoio.
--carregando dados de teste
create table xprod (seqxprod number, col1 varchar2(100), col2 varchar2(100));
insert into xprod values (1,'Tipo 1','1,2,3');
insert into xprod values (2,'Tipo 2','4,5,6,7,8,9,10,11');
insert into xprod values (3,'Tipo 3','12,13,14');
des select * from xprod;
SEQXPROD COL1 COL2
-- --
1 Tipo 1 1,2,3
2 Tipo 2 4,5,6,7,8,9,10,11
3 Tipo 3 12,13,14
des create table xproduto (codprod number, nomeprod varchar2(50));
des select * from xproduto;
CODPROD NOMEPROD
-- --
2 GELADEIRA
10 MESA
12 HOME THEATER
--Crie TYPE auxiliares
create or replace type t_col as object (i number)
/
create or replace type t_nested_table as table of t_col
/
--Funcao para desconcatenar e retornar como TABLE
create or replace function return_table (p_col2 in varchar2) return
t_nested_table as
v_ret t_nested_table;
w_piece varchar2(50);
n number;
w_sql varchar2(200);
begin
--Referencias:
-- http://www.adp-gmbh.ch/ora/plsql/coll/return_table.html
--
v_ret := t_nested_table();
w_sql := p_col2;
loop exit when w_sql is NULL;
n := instr( w_sql, ',' );
w_piece := substr( w_sql, 1, n-1 ); -- 'pescar' a primeira parte
w_sql := substr( w_sql, n+1 ); -- contem o restante da string
if w_piece is null then
--ultima ocorrencia e nao tem mais VIRGULAS, retorna esta e termina
w_piece := w_sql;
w_sql := null;
end if;
--
v_ret.extend;
v_ret(v_ret.count) := t_col(w_piece); -- carrega o vetor com a posicao
pescada acima
--
end loop;
return v_ret;
end return_table;
/
--procurando o tipo do produto
des select a.seqxprod, b.CODPROD,
b.NOMEPROD, a.col1, a.col2 agrupamento
from xprod a,
xproduto b
where b.CODPROD
in (select i valores from table(return_table(col2)))
NM@nmdes /
SEQXPROD CODPROD NOMEPROD COL1 AGRUPAMENTO
-- -- --- -- --
1 2 GELADEIRA Tipo 1 1,2,3
2 10 MESA Tipo 2 4,5,6,7,8,9,10,11
3 12 HOME THEATER Tipo 3 12,13,14
Que tal?
[]´s
Ederson Elias
DBA Oracle
http://br.linkedin.com/pub/ederson-elias/24/8b/8b0
Labor improbus omnia vincit
--- Em oracle_br@yahoogrupos.com.br, Grupos marcio_cbj@... escreveu
Boas.
Estou precisando pesquisar um texto dentro de um campo, e retornar a
informação de outra coluna onde o texto encontra-se.
Por exemplo,
Produto
COL1 COL2
Tipo 1 1,2,3
Tipo 2 4,5,6,7,8,9,10,11
O select ficaria mais ou menos assim:
SELECT COL1 FROM PRODUTO WHERE COD_PRODUTO LIKE COL2 --- (WHERE 4 LIKE
COL2)
Tenho que trazer a informação do Tipo do produto que está na COL2, observem
que os produtos foram cadastrados como uma única string, então dentro de
cada string
RES: [oracle_br] Re: pesquisar texto no campo
Chiappa, realmente a modelagem é uma das piores que já vi.
Na parte WHERE PRODUTO LIKE COL2, o campo PRODUTO está vindo de outra
tabela.
Assim, eu tenho uma tabela de vendas onde consta os códigos dos produtos que
foram vendidos, e esse código eu tenho que pesquisar naquele campo varchar
que está em outra tabela, encontrando o código eu retorno a categoria do
produto que também faz parte dessa tabela também.
O select ficaria mais ou menos assim:
SELECT P.DESC_CATEGORIA
FROM VENDAS V, PROD_CATE P
WHERE V.COD_PRODUTO LIKE P.LST_PROD
EXEMPLO DAS TABELAS
VENDAS:
COD_VEND
COD_CLI
COD_PRODUTO
VLR_PROD
PROD_CATE
COD_PROD
DESC_CATEGORIA
LST_PROD
VALORES DA PROD_CATE
COD_PRODDESC_CATEGORIA LST_PROD
G4 TERCEIROS 0400,0401,0550,7123
Grato.
De: oracle_br@yahoogrupos.com.br [mailto:oracle_br@yahoogrupos.com.br] Em
nome de J. Laurindo Chiappa
Enviada em: segunda-feira, 7 de outubro de 2013 18:52
Para: oracle_br@yahoogrupos.com.br
Assunto: [oracle_br] Re: pesquisar texto no campo
Bom, antes de responder a gente ** TEM ** que comentar que quem modelou isso
fumou alguma coisa esquisita - tem um Monte de coiisas questionáveis nesse
modelo, entre elas :
1) certamente pelo que vc mostra vc tem valores Numéricos (separados por
vírgula, mas numéricos) numa coluna string : ou seja, números dentro de
string, numa tacada só vc já mandou a validação natural de datatype pro
saco, Confiando que o sistema vai conferir que os números entrados são
números... argh
2) vc caiu aí num limite ARTIFICIAL : QUANDO (e não SE, mas QUANDO) for
preciso armazenar mais e mais valores, vc VAI cair no limite máximo de
caracteres da coluna...
3) a linguagem SQL possui uma ENORME riqueza de funcionalidades para vc
pesquisdar valores numa tabela (EXISTs, IN/SUB-QUERIES, JOINs, etc, etc) ,
que não dá NEM para comparar em facilidade de uso (E PERFORMANCE!!) para
casos mais complexos de value matching e grandes quantidades...
== TODOS os problemas (falhas/omissões/idéias jericosas, na verdade)
acima seriam fácil e simplesmente Solucionados fazerndo a coisa certa,
fazendo aquilo que os databases Existem para fazer, ie : vc teria uma TABELA
com os valores inseridos numa coluna e cada valor seria um registro na
tabela Com isso vc matou a validação (a coluna teria o datatype NUMBER)
** e ** ao mesmo tempo, já que a tabela (como Qualquer Outra) não tem um
limite máximo facilmente alcancável, vc não cairia em limites
ARTIFICIALMENTE impostos por uma modelagem furada, ** E ** teríamos a nosso
favor a riqueza de comandos da linguagem SQL, bem como a Otimização feita
pelo CBO, que só funciona em SQLs entre tabelas (Rigorosamente Não Tem muito
o que vc otimizar operações dentro de uma só string - vc pode tentar as
várias funções de manipulação de strings, mas NINGUÉM assegura nada)
Eu se sou o DBA daria umas lambadas no analista que bola essas coisas, que
são ** TÍPICAS ** de gente que pensa em XMl apenas, que não programa COM
databases, mas sim CONTRA os databases
Isso posto : pelo que entendi, vc quer pegar o número que está no final da
string da coluna1 E buscá-lo dentro da string contida na coluna2, okdoc ??
Tá meio BASTANTE OBSCURO esse ...PRODUTO LIKE COL2 --- (WHERE 4 LIKE
COL2)... mas é o que eu entendi
Sendo isso, existem diversas maneiras de vc pesquisar uma substring numa
string maior, como osa operadores IN ou o LIKE, mas imho na versão 10g o
mais direto são as EXPRESSÕES REGULARES O grande lance é que a função
própria para isso, a REGEXP_SUBSTR, busca uma ocorrência/posição por vez
Se vc soubesse que há no máximo, digamos, 7 valores possíveis em cada string
comma-delimited, vc até poderia mandar um REGEXP_SUBSTR na ocorrência 1, um
na ocorrência 2, etc, até a 7, mas imagino não ser o caso : o Truque aqui é
** contar ** a quantidade de vírgular e gerar um loop de valores de 1 até
essa contagem... No exemplo abaixo usarei o INFINITE DUAL para isso , aí
ficaria + ou - assim :
== crio massinha de dados :
SCOTT@O10GR2:SQLCREATE TABLE TEST (C1 VARCHAR2(10), C2 VARCHAR2(40));
Tabela criada.
SCOTT@O10GR2:SQLinsert into test values ('Tipo 1', '1,2,3');
1 linha criada.
SCOTT@O10GR2:SQLinsert into test values ('Tipo 2', '4,5,6,7,8,9,10');
1 linha criada.
SCOTT@O10GR2:SQLinsert into test values ('Tipo 3', '3,4,5');
1 linha criada.
SCOTT@O10GR2:SQL
== agora o exemplo da consulta que desmonta a lista de valores - embora
Absolutamente Desnecessário, por didática vou meter um ORDER BY e mostrar a
coluna n gerada para representar a ocorrência:
SCOTT@O10GR2:SQLselect test.c1,
2 regexp_substr ( test.c2, '[^,]+', 1, n) as num,
3 n
4 from test,
5 (select level n
6 from (select max ( length (c2) - length (replace (c2, ','))) as max_commas
7 from test
8 )
9 connect by level = 1 + max_commas
10 ) ctr
11 where
12 ctr.n = 1 + length (c2) - length (replace (c2, ','))
13* order by 1, to_char(num, 'FM009');
C1 NUM N
RES: [oracle_br] Re: pesquisar texto no campo
Ederson, adaptei no meu select e aparentemente está certo. Vou realizando mais testes, com mais produtos e dou feedback posteriormente. Grato. De: oracle_br@yahoogrupos.com.br [mailto:oracle_br@yahoogrupos.com.br] Em nome de ederson2001br Enviada em: terça-feira, 8 de outubro de 2013 10:21 Para: oracle_br@yahoogrupos.com.br Assunto: [oracle_br] Re: pesquisar texto no campo Marcio, Gostei do desafio, apesar de achar a modelagem um tanto confusa. Depois do exemplo do Chiappa (que sempre é um gatilho mais ligeiro), terminei o meu código e resolvi postar apenas para servir de complemento para você. Vamos lá: criei uma tabela XPROD, XPRODUTO e carreguei com seu exemplo, depois criei dois TYPE e uma FUNCTION de apoio. --carregando dados de teste create table xprod (seqxprod number, col1 varchar2(100), col2 varchar2(100)); insert into xprod values (1,'Tipo 1','1,2,3'); insert into xprod values (2,'Tipo 2','4,5,6,7,8,9,10,11'); insert into xprod values (3,'Tipo 3','12,13,14'); des select * from xprod; SEQXPROD COL1 COL2 -- -- 1 Tipo 1 1,2,3 2 Tipo 2 4,5,6,7,8,9,10,11 3 Tipo 3 12,13,14 des create table xproduto (codprod number, nomeprod varchar2(50)); des select * from xproduto; CODPROD NOMEPROD -- -- 2 GELADEIRA 10 MESA 12 HOME THEATER --Crie TYPE auxiliares create or replace type t_col as object (i number) / create or replace type t_nested_table as table of t_col / --Funcao para desconcatenar e retornar como TABLE create or replace function return_table (p_col2 in varchar2) return t_nested_table as v_ret t_nested_table; w_piece varchar2(50); n number; w_sql varchar2(200); begin --Referencias: -- http://www.adp-gmbh.ch/ora/plsql/coll/return_table.html -- v_ret := t_nested_table(); w_sql := p_col2; loop exit when w_sql is NULL; n := instr( w_sql, ',' ); w_piece := substr( w_sql, 1, n-1 ); -- 'pescar' a primeira parte w_sql := substr( w_sql, n+1 ); -- contem o restante da string if w_piece is null then --ultima ocorrencia e nao tem mais VIRGULAS, retorna esta e termina w_piece := w_sql; w_sql := null; end if; -- v_ret.extend; v_ret(v_ret.count) := t_col(w_piece); -- carrega o vetor com a posicao pescada acima -- end loop; return v_ret; end return_table; / --procurando o tipo do produto des select a.seqxprod, b.CODPROD, b.NOMEPROD, a.col1, a.col2 agrupamento from xprod a, xproduto b where b.CODPROD in (select i valores from table(return_table(col2))) NM@nmdes / SEQXPROD CODPROD NOMEPROD COL1 AGRUPAMENTO -- -- --- -- -- 1 2 GELADEIRA Tipo 1 1,2,3 2 10 MESA Tipo 2 4,5,6,7,8,9,10,11 3 12 HOME THEATER Tipo 3 12,13,14 Que tal? []´s Ederson Elias DBA Oracle http://br.linkedin.com/pub/ederson-elias/24/8b/8b0 Labor improbus omnia vincit --- Em oracle_br@yahoogrupos.com.br, Grupos marcio_cbj@... escreveu Boas. Estou precisando pesquisar um texto dentro de um campo, e retornar a informação de outra coluna onde o texto encontra-se. Por exemplo, Produto COL1 COL2 Tipo 1 1,2,3 Tipo 2 4,5,6,7,8,9,10,11 O select ficaria mais ou menos assim: SELECT COL1 FROM PRODUTO WHERE COD_PRODUTO LIKE COL2 --- (WHERE 4 LIKE COL2) Tenho que trazer a informação do Tipo do produto que está na COL2, observem que os produtos foram cadastrados como uma única string, então dentro de cada string tem vários códigos. Não sei se fui claro, qualquer coisa tento explicar melhor. Oracle 10.2.0.5
[oracle_br] Re: pesquisar texto no campo
Bom, antes de responder a gente ** TEM ** que comentar que quem modelou isso
fumou alguma coisa esquisita - tem um Monte de coiisas questionáveis nesse
modelo, entre elas :
1) certamente pelo que vc mostra vc tem valores Numéricos (separados por
vírgula, mas numéricos) numa coluna string : ou seja, números dentro de string,
numa tacada só vc já mandou a validação natural de datatype pro saco, Confiando
que o sistema vai conferir que os números entrados são números... argh
2) vc caiu aí num limite ARTIFICIAL : QUANDO (e não SE, mas QUANDO) for
preciso armazenar mais e mais valores, vc VAI cair no limite máximo de
caracteres da coluna...
3) a linguagem SQL possui uma ENORME riqueza de funcionalidades para vc
pesquisdar valores numa tabela (EXISTs, IN/SUB-QUERIES, JOINs, etc, etc) , que
não dá NEM para comparar em facilidade de uso (E PERFORMANCE!!) para casos mais
complexos de value matching e grandes quantidades...
== TODOS os problemas (falhas/omissões/idéias jericosas, na verdade)
acima seriam fácil e simplesmente Solucionados fazerndo a coisa certa, fazendo
aquilo que os databases Existem para fazer, ie : vc teria uma TABELA com os
valores inseridos numa coluna e cada valor seria um registro na tabela Com
isso vc matou a validação (a coluna teria o datatype NUMBER) ** e ** ao mesmo
tempo, já que a tabela (como Qualquer Outra) não tem um limite máximo
facilmente alcancável, vc não cairia em limites ARTIFICIALMENTE impostos por
uma modelagem furada, ** E ** teríamos a nosso favor a riqueza de comandos da
linguagem SQL, bem como a Otimização feita pelo CBO, que só funciona em SQLs
entre tabelas (Rigorosamente Não Tem muito o que vc otimizar operações dentro
de uma só string - vc pode tentar as várias funções de manipulação de strings,
mas NINGUÉM assegura nada)
Eu se sou o DBA daria umas lambadas no analista que bola essas coisas,
que são ** TÍPICAS ** de gente que pensa em XMl apenas, que não programa COM
databases, mas sim CONTRA os databases
Isso posto : pelo que entendi, vc quer pegar o número que está no final da
string da coluna1 E buscá-lo dentro da string contida na coluna2, okdoc ?? Tá
meio BASTANTE OBSCURO esse ...PRODUTO LIKE COL2 --- (WHERE 4 LIKE COL2)...
mas é o que eu entendi
Sendo isso, existem diversas maneiras de vc pesquisar uma substring numa
string maior, como osa operadores IN ou o LIKE, mas imho na versão 10g o mais
direto são as EXPRESSÕES REGULARES O grande lance é que a função própria
para isso, a REGEXP_SUBSTR, busca uma ocorrência/posição por vez Se vc
soubesse que há no máximo, digamos, 7 valores possíveis em cada string
comma-delimited, vc até poderia mandar um REGEXP_SUBSTR na ocorrência 1, um na
ocorrência 2, etc, até a 7, mas imagino não ser o caso : o Truque aqui é **
contar ** a quantidade de vírgular e gerar um loop de valores de 1 até essa
contagem... No exemplo abaixo usarei o INFINITE DUAL para isso , aí ficaria +
ou - assim :
== crio massinha de dados :
SCOTT@O10GR2:SQLCREATE TABLE TEST (C1 VARCHAR2(10), C2 VARCHAR2(40));
Tabela criada.
SCOTT@O10GR2:SQLinsert into test values ('Tipo 1', '1,2,3');
1 linha criada.
SCOTT@O10GR2:SQLinsert into test values ('Tipo 2', '4,5,6,7,8,9,10');
1 linha criada.
SCOTT@O10GR2:SQLinsert into test values ('Tipo 3', '3,4,5');
1 linha criada.
SCOTT@O10GR2:SQL
== agora o exemplo da consulta que desmonta a lista de valores - embora
Absolutamente Desnecessário, por didática vou meter um ORDER BY e mostrar a
coluna n gerada para representar a ocorrência:
SCOTT@O10GR2:SQLselect test.c1,
2 regexp_substr ( test.c2, '[^,]+', 1, n) as num,
3 n
4from test,
5(select level n
6 from (select max ( length (c2) - length (replace (c2, ',')))
as max_commas
7 from test
8)
9 connect by level = 1 + max_commas
10 ) ctr
11 where
12 ctr.n = 1 + length (c2) - length (replace (c2, ','))
13* order by 1, to_char(num, 'FM009');
C1 NUM N
-- -- ---
Tipo 1 11
Tipo 1 22
Tipo 1 33
Tipo 2 41
Tipo 2 52
Tipo 2 63
Tipo 2 74
Tipo 2 85
Tipo 2 96
Tipo 2 10 7
Tipo 3 31
Tipo 3 42
Tipo 3 53
13 linhas selecionadas.
SCOTT@O10GR2:SQL
== legal ?? Agora (SEMPRE SUPONDO, como disse antes, que o que vc quer é match
do que vem depois do Tipo na c1 contra o valor extraído da CSV, que está na
coluna NUM no meu caso-montado) é um WHERE simples, tipo :
SELECT * FROM (queryquetrazalistademonstada)
WHERE stringfinaldepoisdoespaço = NUM;
= botando no esqueleto (E exibindo o substr só para fins didáticos) :
SCOTT@O10GR2:SQLed
Gravou file afiedt.buf
1 select c1, num, substr(c1, -1, instr(c1, ' '))
2 from (select test.c1,
3 regexp_substr (
