LIKE and IN command in the same SQL command

Asked

Viewed 8,105 times

1

It is possible to use like and in in the same SQL command? For example, I need to search in the table stockpile the Serial Number that can be 'SCAB171293E29','SCAB171293E4E' but they might have something else at the end so I thought of something like this:

Select numeroserie from public.estoque where numeroserie like in ('SCAB171293E29%','SCAB171293E4E%')

But obviously it was wrong. Are there any way to accomplish this select?

database postgresql

  • one OR does not solve the problem?

  • @rray Serão n serial numbers

3 answers

6


You can enter an array containing the beginning of the serial number you want to search as below:

select *
from estoque
where numero_serie like any(array['SCAB171293E29%','SCAB171293E4E%']);

  • That’s exactly what I needed, thanks for your help

2

You need to separate in 3 conditions: numeroserieequal to 'SCAB171293E29' or 'SCAB171293E4E', or numeroserie similar to 'SCAB171293E29%', or serial number similar to 'SCAB171293E4E%'. The result would be:

select numeroserie from public.estoque where numeroserie in ('SCAB171293E29','SCAB171293E4E') or numeroserie like 'SCAB171293E29%' or numeroserie like 'SCAB171293E4E%'.

With more robust checks it is always interesting to think atomic way to find the final solution. So you think of each verification criterion separately and then it becomes easier to join them to the final solution.

  • 1

    The problem is they will be n serial numbers so I can’t put it that way

  • But that would be the same solution adding the OR clauses. You just need to think of an idea to build the query, using a Java program, for example.

  • Yes is exactly what I do in the solution proposed by @Camilosantos, the only difference between your answers was the delay to query finished executing, in his idea was almost instantaneous already in his idea it took a long time to finalize the query

0

Well if the serial number size is bigger, it makes no difference since you are using the % (after all it serves precisely for this)
Maybe you are expressing yourself badly, I believe your doubt is, in some cases your in will have 3 serial numbers and in others will have 15, for example.
You can do it like this:

SELECT * FROM TABELA WHERE numeroserie IN (SELECT NUMEROSERIE FROM OUTRA_TABELA)

If this is not yet the case, you should then separate into several clauses. For example, when the number size is 5 vc does with the IN, when it is 6, you do with the %.
Follow an example:

SELECT * FROM TABELA WHERE numeroserie IN ('numeroserie','numeroserie2') OR numeroserie like '%numeros%' OR numeroserie like '%serie'

Browser other questions tagged database postgresql

You are not signed in. Login or sign up in order to post.