0

I'm running a query where I'm selecting records that contain a word in a VarChar2(4000) field. The query works but only for some dates and not others.

I've tried REGEXP_LIKE and INSTR. I've tried it in SQL Developer and ACCESS and I get the same results.

Oracle:

REGEXP_LIKE(Table, 'Text') 
INSTR(Table,'Text', 1)<>0

Access:

(((Table) Like "*" & "Text" & "*"));

Case sensitivity is not the issue.

Has anyone else seen an case like this before?

Edited to show sample data:

**Job    Date        Description**
1       6/6/2017      Blah Blah Text Blah      Query works and finds Text
2       6/6/2018      Blah Blah Text Blah      Query can't find Text

The same query works for any date prior to 2018.

LifHkr
  • 35
  • 5
  • 2
    Sample data and expected result will go a long way – user8834780 Oct 05 '18 at 18:43
  • When using the Oracle `REGEXP_LIKE()` function the second parameter should be a valid regular expression. In this case something like `'.*Text.*'`. – The Impaler Oct 05 '18 at 18:45
  • 2
    @TheImpaler . . . `'Text'` *is* a valid regular expression. It will match "Text" anywhere in the column. – Gordon Linoff Oct 05 '18 at 18:48
  • Does the following Access query return only the first of those 2 sample rows? `SELECT * FROM YourTableNameHere WHERE [Description] ALike '%Text%'` – HansUp Oct 05 '18 at 19:12
  • @GordonLinoff I thought he wanted to search for a "word": that is, that specific sequence of letters surrounded by blank delimiters. My example was too simplistic. My bad. – The Impaler Oct 05 '18 at 19:22
  • Both ACCESS and Oracle provide the same results. The query works but only for records with a date before 2018. – LifHkr Oct 05 '18 at 19:40
  • 2
    Can you also show your actual **query**? What does the date column even have to do with your problem at all? Meaning - it is much more likely that you are doing something *else* in the query that causes whatever you think is wrong with the output. –  Oct 05 '18 at 20:22

0 Answers0