Query two or more words in a string

Asked

Viewed 288 times

2

I need to make mine select bring only records whose description has the words "RACAO" and "ROYAL". I’m using Regex to make it easier, but I don’t know why you’re bringing in records that have nothing to do with the search.

inserir a descrição da imagem aqui

select * from cadpro where descricao ~ '^[RACAO ROYAL].*'
sql postgresql regex

  • Why not use the operator % ? Ex.: select * from cadpro where descricao like '%RACAO%ROYAL%'

  • Hello @Mauro Rocha! The problem of this approach is that it only queries if within a line there are two words. Lines that contain one of the quoted words are not being brought in. I need him to bring the records that have the two words or that contain at least one of the words.

  • I don’t know if I understand, but is it not descricao ~ '^[RACAO|ROYAL].*'?

  • I have tried to use pipeline by separating words, but it does not change the result.

  • Try it this way: '^(admin|com).*'

  • 1

    @Maurorocha thus worked: select * from cadpro Where Description ~ ' (RACAO|ROYAL). *'. Put the solution I complete and mark you. Thanks Personal!

Show 1 more comment

1 answer

1


Just to explain why your regex didn’t work:

The brackets define a character class, that is, the expression will give match in any of the characters between [ and ]. For example, [abc] means "the letter a, or the letter b, or the letter c" (only one of them) - is an expression that corresponds to only one character.

Therefore [RACAO ROYAL] means "the letter R, or the letter A, or the letter C, or the letter A (again, so it’s redundant to have the A twice here), or the letter O, or a space, or the letter R (again), etc".

And how you used the bookmark ^, meaning "beginning of the string", this regex brings all records starting with any of these characters (so it brings the columns starting with "C", with "R", etc).

If you want to check two different options, and these options have more than one character, the solution (as already suggested to another answer) is to use alternation: the character |, which means "or". Then I would stay:

select * from cadpro where descricao ~ '^(RACAO|ROYAL).*';

Changing the brackets by parentheses works because the brackets group the expression RACAO|ROYAL (or is "RACAO", or is "ROYAL"). Thus, regex only considers records starting with "RACAO" or "ROYAL".

Without the parentheses the expression would be ^RACAO|ROYAL.*, and because of the operators' precedence, it would mean "starts with RACAO, or has ROYAL (anywhere, not necessarily at the beginning)". See the difference here and here. That’s why parentheses are needed.

Note: use [RACAO|ROYAL], as suggested in the comments, does not solve, because it will also bring the records that begin with |. This is even a very common error when using regex: find that the brackets take the whole text, when in fact what is inside them is just a list of characters to be considered (ie, [RACAO ROYAL] and [ACORYL ] are equivalent in that they regard the same characters in the same way as [RACAO|ROYAL] and [ACORYL|] are also equivalent). And inside the brackets, many special characters such as the |, "lose their powers" and become common characters with no special meaning.

Since "R" repeats in both options, another alternative is:

select * from cadpro where descricao ~ '^R(ACAO|OYAL).*';

But as in this case you want the records that start with "RACAO" or start with "ROYAL", I think you don’t even need regex:

select * from cadpro where descricao like 'RACAO%' or descricao like 'ROYAL%';

See here the above working queries.

If you want the consultation to be case insensitive (do not differentiate between upper and lower case letters), just change the operator ~ for ~* (as set out in documentation), or change the like for ilike.

Browser other questions tagged sql postgresql regex

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