CODEWITHSUNDEEP
javasqldatabaseoracle-database
2dor
2dor

Reputation: 1001

SQL for matching input string on a given column that stores patterns

In Oracle db, I have a table let's say "test_table". This table has a column "test_column" that stores patterns like ^(0[1-9]|[12][0-9]|3[01]$, Batman*, etc.

I have then an input in my Java app with value "25".

What is the best way to find if my input string matches any row in my test_table ? Is there an easy query I can do or do I have to write procedure or get all data and filter in the code?

Upvotes: 0

Views: 61

Answers (2)

Littlefoot
Littlefoot

Reputation: 142713

As far as I understood the question, i.e.

find if my input string matches any row in my test_table

then - wouldn't it be an ordinary

select * 
from test_table
where test_column = :input_value

That might need to be modified, depending on what "match" means to you. For example, this might be yet another option, which would check whether stored values contain input value:

select * 
from test_table
where instr(test_column, :input_value) > 0

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269773

It sounds like you want regexp_like():

select t.*
from test_table t
where regexp_like(:input, t.test_column);

Upvotes: 1

Related Questions