Oracle SQL: Searching for $ in Regexp

Question

I want to search my response field to find any instance where a dollar sign $ is not followed by a numerical value. 1 or 2 spaces before a numerical value is ok, but there shouldn't be any text values following $.

I have the following query below:

SELECT * FROM RESPONSES
WHERE (regexp_like(response, '(\$)(\s){1,2}[^0-9]'));

This should be able to identify responses that have "$ NA". Most responses will contain a combination of $ followed by numeric values and $ by text values.

I've tried a couple of variations of the above query without any success. Any thoughts?

Answer 1

You can use this:

SELECT  *
FROM dual
WHERE
SIGN(REGEXP_INSTR (RESPONSE, '(\$)(\s){2}[^0-9]'))=0 

Answer 2

You may use :

select * from responses where regexp_like(a, '^\$\s')

to get values begin with a $ sign and followed with at least one space as consecutive character(s).

with t as
(
 select '$ 524' as a from dual union all 
 select '$524' as a from dual union all 
 select '$  s67e' as a from dual union all  
 select '# 67e' as a from dual union all    
 select '$s67e' as a from dual union all
 select '$#67e' as a from dual 
 ) 
select * from t where regexp_like(a, '^\$\s')
 A
---- 
$ 524
$ s67e

Demo

Answer 3

Include the space character in your negated character set

Since a space character qualifies as a non-digit character, a second space can give a "false positive" for the data set you want to find.

SCOTT@db>WITH smple AS (
  2      SELECT
  3          '23  dkf $ 1' response
  4      FROM
  5          dual
  6      UNION ALL
  7      SELECT
  8          '23 dkfg gjg $  4'
  9      FROM
 10          dual
 11      UNION ALL
 12      SELECT
 13          '$ NA'
 14      FROM
 15          dual
 16  ) SELECT
 17      s.*
 18    FROM
 19      smple s
 20    WHERE
 21      ( REGEXP_LIKE ( s.response,
 22      '\$\s{1,2}[^ 0-9]+' ) );
RESPONSE
----------
$ NA