Find STRING NUMBER

[jks1903]

Personal is the following.

I have a MCampo in a table that is kindly.

is a field for observations, but we are using the same with the intention of relating codes with another system.

In this field, there may be as follows:

1-33-566-23-230

Where each number It's a code. I wanted to see if someone has a role or script ready to locate within that field a certain number.

For example: if I search the observation with the code 23, it seeks the line where one of the related codes is the 23.

to break the head And I'm not finding a way to do this.
I tried with% 23% for example, but then he returns to 233 também.

Does anyone have an idea of ??how can I get it?

[diegopedrao]

cara V C é This:

Select all

 
select SUBSTR('34983024230892389809808',INSTR('34983024230892389809808', '23') , LENGTH('23')) from dual 

Att,

Diego Monteiro

[jks1903]

Dude, maybe it's, but only with this example I can not say.

shows me an example on swguento structure then, then I can see.

Let's say I have Table X

in this table X I have the COD and COD_RELAC field

COD is a Sequence (1,2,3,4 ...)

COD_RELAC is the VARCHAR field where there may be the string (1-2-33-22-45)

Let's say I want to locate the line where in the COD_RELAC field, it has the number 33.

How would SELECT?

vlw

[victorhugomuniz]

Can not you do that?

Select all

select * from x where cod_relac like '%-33-%';

[diegopedrao]

See this here then easier.

Select all

 
 
SELECT CAMPO 
  FROM (select '33-2-1-22-23' as campo from dual) 
 WHERE CAMPO LIKE '%-33-%' 
    OR CAMPO LIKE '%-33' 
    OR CAMPO LIKE '33-%' 

ATT,

Diego Monteiro

[jks1903]

Dude, hahahaha

My problem should be sleep, I'm at the time of evidence and jobs in college and more the trab here that is pulled, I do not know why I could not think about it, lol.

really very simple, old vlw ...

[Marcelo Malta]

The solution is simple yes, but I do not see much difference between it and

Select all

like '%33%'

if you have any code '1-29-18-333-182-91' will bring too, and I think it is not What you want, it picks up by '% -33%'

I would have to search to give you a correct answer, but there is a function that separates the values ??by a certain character, in case The '-', type the split in C # and brings a list of values. I can not remember what it's like in Oracle, but I guess it would suit you better.

If anyone knows what I'm talking about could help as well.

[jks1903]

Dude, in fact that way there will not get a possible -333- because the like always has the trait.

or is% -33-% that sought 33 in the middle of string
or '33 -% which will seek at the beginning of it
or at last the % -33 'which will find if the 33 is at the end.

For always having the trace with 33, there is no danger of seeking a 333.

But VLW for the answer.

: -O

[victorhugomuniz]

I think there is no such function .. it has to create it
http://www.orafaq.com/forum/t/11692/0/

[gfkauer]

How about this:

Select all

Select g.* 
  From gfk_teste g 
 Where g.valor Like '%&p_busca%' 
   And ((instr(g.valor, &p_busca) + length(&p_busca)-1) = length(g.valor) 
     Or substr(g.valor, instr(g.valor, &p_busca) + length(&p_busca), 1) = &separador); 

[burga]

The problem of the diego code is if there is only the number 33 in the field (no traces before or after), it will not be returned.

I think the simplest solution is to use regular expressions:

Select all

select * from tabela 
where regexp_like(coluna, '(^|-)33($|-)');

[Marcelo Malta]

I really ate fly. Your solution meets well, except for the case of only having the number 33 in the field.

However they have already put a solution to this.

[jks1903]

The problem of the diego code is if there is only the number 33 in the field (no traces before or after), it will not be returned.

I think the simplest solution is to use regular expressions:

OPA There was no more facing the topic for already having solved the problem.
But really if it is only 33 that code did not seek.

But then I simply added another or

Select all

 
... 
OR campo LIKE '33' 

and worked blza, I created a function returning the code of this select and I was able to solve the problem .

Thanks to all for help.

vlw.