Regexp_like check for symbol in string

Question

If I want to check that string contains @symbol I can write something like

REGEXP_LIKE(source_column,'@')

or

REGEXP_LIKE(source_column, '.*@.*')

What is the difference between these two forms? And why REGEXP_LIKE(source_column,'@') returns true even if string has other symbols than @? For example it matches with mail@mail.com and 12@

Naturally '@' looks like exact string match for me, and '.*@.*' I read as 'any string with that symbol'.

score 3 · Accepted Answer · answered Apr 13 '16 at 12:55

These three all function identically and will return true if any number of characters precede or follow the @ symbol:

REGEXP_LIKE(source_column,'@')
REGEXP_LIKE(source_column,'.*@.*')
REGEXP_LIKE(source_column,'^.*@.*$', 'n')

(You need the 'n' match parameter for the last example if you have multi-line data otherwise the . wildcard character will not match newlines and the match will fail.)

If you want an exact match then look for the start-of-string (^) and end-of-string ($) immediately preceding and following the symbol:

REGEXP_LIKE(source_column,'^@$')

Regexp_like check for symbol in string

1 Answers1