c# search oracle table column for regex pattern

Question

I'm trying to build an asp.net page using c# that will query a column in an Oracle database that has 20,000 rows.

I want to display all rows that match this regular expression pattern "[\r\n]$".(should only have about 5 rows that match this pattern)

The version of Oracle we use does not support regex so I need to find a way to do this in c# but I'm not sure how do that.

Answer 1

If you WANT to do it using regex in .net you would basically have to load all data into memory and perform the regex search there. This could potentially lead to very poor performance. I think you should consider doing normal string comparison in the database if at all possible.

Answer 2

First, start by figuring out what the regular expression does.

Let's break this one down:

[\r\n]$

The brackets [] give you a character class and says "match one of these characters." Thus [\r\n] says match \r (carriage return) or \n (line break). The $ is an anchor and says "match at the end of the string." Thus, the regular expression says "match a carriage return or line break at the end of the string."

Now, can you translate that into SQL? For this you need something like

SELECT * FROM TABLE
WHERE
    SOMECOLUMN LIKE '%'||CHR(13)||CHR(10) OR
    SOMECOLUMN LIKE '%'||CHR(13)

(Sorry, my Oracle is weak but this is a close first approximation.)

If not, can you get the data out of your table (SELECT * FROM TABLE) and run this regex locally in .NET? Twenty-thousand rows is not that much to just pull the whole thing into memory and run it locally.