CODEWITHSUNDEEP
regexoracle-databaseregexp-like
gm08
gm08

Reputation: 29

"regexp-like" in Oracle returning inaccurate results

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

Upvotes: 0

Views: 114

Answers (1)

SierraOscar
SierraOscar

Reputation: 17637

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"

Upvotes: 1

Related Questions