0

I am having trouble in retrieving the correct data for a column, using regular expressions in Oracle 11.

The column is of type varchar, and looks like this:

2216xxxx
20xxxx
2355xxxx
2128xxxx
213xxxx
692xxxx

I am using this part of the query:

regexp_like (column_name, '^(?216*|?213*|?218*|?212*|?249*|?20*)')

to filter out the rows that begin with 20, 216, 213, 218, 212 and 249, or the ones that start with these prefixes, preceded by a 2 ( eg. 220, 2216, 2213, etc) but in the results I am also getting records starting with 2355.

I am sure that I have made some mistake with the regex, but I am not being able to find it.

Some help would be very appreciated.

P.S

I know that I could go with a series of "or column_name like..." but I thought that using regexs would be a better solution

gm08
  • 29
  • 8
  • 1
    `?` denotes "0 or 1 of" a group or character - try removing all the `?` and see what happens – SierraOscar Nov 25 '15 at 14:57
  • @MacroMan I tried to remove it, but I still get these records starting with 2355. When I first added ?, I wanted to include that 2, which in some cases happens to be, and in some others not... – gm08 Nov 25 '15 at 15:04

1 Answers1

1

You're using ? and * incorrectly. use this instead:

^(216.*|213.*|218.*|212.*|249.*|20.*)

You don't need ? and you need to use .* which means "any character (except new line), between zero and an unlimited amount of times"

SierraOscar
  • 17,507
  • 6
  • 40
  • 68
  • This time it removed the unwanted records, the ones starting with `2355`, but it also removed the records that I wanted to include, the ones starting with the above prefixes, but having a digit `2` at the beginning. In a few words, I want have all the records that start `216` or `2216`, and so on... – gm08 Nov 25 '15 at 15:16
  • Add an optional 2 in front of the group.`'^(2)?(216.*|213.*|218.*|212.*|249.*|20.*)'`. Test thoroughly! – Gary_W Nov 25 '15 at 15:24
  • 1
    Why repeat `.*` and `2` so many times? `'^2?2(16|13|18|12|49|0).*'`. Or even `'^2?2(1[6382]|49|0).*'` – Wiktor Stribiżew Nov 25 '15 at 15:31
  • Nice but I'd have to argue that for a developer that is less experienced in regular expressions, the original one communicates the desired intention better even though it is not as slick. Whichever you choose, gm08, do yourself and the person that will be maintaining this after you a favor and document what this is doing, no matter how well you think it does this itself! Include a link to this post in the comment as well. – Gary_W Nov 25 '15 at 15:55
  • @stribizhev that is much shorter - but I'd argue that if the OP doesn't understand it then it hasn't served it's purpose as an answer. Plus what's a couple of "2"s between friends?! :P – SierraOscar Nov 25 '15 at 15:57
  • Anyway, `.*` should be really used once. – Wiktor Stribiżew Nov 25 '15 at 16:00
  • Thank you very much guys, it works very well. You are right Gary_W, I have to write it down what has happened :) – gm08 Nov 25 '15 at 16:04
  • 1
    Yes, always document for the person maintaining after you. – Gary_W Nov 25 '15 at 16:10