-2

In one of my query I do not want Regular Expression condition in WHERE clause. It means the query should exclude Regular expression condition. Eg:- select ........ where regexp_like(col, '[a-zA-Z0-9]\.[a-zA-Z0-9]')

whatever we are getting records from the above query I do not want. In the above code i do not want this regular expression in the WHERE clause. How to exclude this condition so that i can filter my condition & query .

Shahin P
  • 372
  • 1
  • 4
  • 14
  • 3
    Describe what you're trying to do! – jarlh Feb 28 '18 at 07:50
  • You can't easily do the same logic as your current regex is doing, without using a regex. – Tim Biegeleisen Feb 28 '18 at 07:51
  • That above query is not working, it shows an error because you use it (regex) the wrong way in oracle. Read the manual of `REGEXP_LIKE` first then try again later. – Pham X. Bach Feb 28 '18 at 07:54
  • maybe you can try `where columnName not like '[a-zA-Z]%' ` it's working on mssql – Harun KARATAŞ Feb 28 '18 at 08:01
  • @HarunKARATAŞ Not working that way in oracle – GeorgiG Feb 28 '18 at 08:39
  • 1
    We would prefer a question that asks what you want rather than what you don't want – Kaushik Nayak Feb 28 '18 at 08:39
  • Heh, that's funny. You don't want to use regular expression *because it shows an error*. I'd rather try to fix that *error*. It is obvious that `REGEXP_LIKE` you posted is invalid; a quick view at the documentation would reveal why. If you could describe what you want (as @Kaushik has already said), it would be easier to assist. – Littlefoot Feb 28 '18 at 08:56
  • @TimBiegeleisen according to the logic if I use `regexp_like` then i find only selected data which do not want. It means exclude the data which is get from `regexp_like`. – Shahin P Feb 28 '18 at 09:34
  • Wanting to keep or exclude based on a regex does not change that you need to use a regex. – Tim Biegeleisen Feb 28 '18 at 09:37
  • @TimBiegeleisen If in a table 500 records are there and when i use `regexp_like` in the WHERE clause then i got 100 records. But i do not want these 100 records. I want remaining 400 records. – Shahin P Feb 28 '18 at 09:40
  • 2
    Then just remove NOT. – Tim Biegeleisen Feb 28 '18 at 09:47
  • 1
    Please **[edit]** your question and add some [sample data](http://plaintexttools.github.io/plain-text-table/) and the expected output based on that data. [Formatted text](http://stackoverflow.com/help/formatting) please, [no screen shots](http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557). ([edit] your question - do **not** post code or additional information in comments) –  Feb 28 '18 at 09:54
  • @TimBiegeleisen NOT has been remove but i do not want the criteria – Shahin P Feb 28 '18 at 10:39

1 Answers1

1

Are you looking for not?

where not regexp_like(col, '[a-zA-Z0-9]\.[a-zA-Z0-9]')

You might need to take NULLs into account as well:

where not regexp_like(col, '[a-zA-Z0-9]\.[a-zA-Z0-9]') or col is null