Oracle Regex query for specific pattern -

could me in framing regex query filters following pattern among different pattern column.

pattern: firstname1 lastname1#firstname2 lastname2

eg: george david#william david

for below written query, firstname1 lastname1#firstname2 lastname2 alongwith name1#name2 patterns too.

query:

select column1 table regexp_like(column1, '(^|\s|\w)#($|\s|\w)'); 

the '(^|\s|\w)#($|\s|\w)' pattern matches start of string, whitespace, or non-word char, #, , end of string, whitespace, or non-word char. not restrict context here , match # inside string not enclosed word chars.

you need anchors , more specific pattern:

'^\w+\s+\w+#\w+\s+\w+$' 

you may further adjust allow apostrophes or hyphens if necessary changing \w+ \w+([''-]\w+)* (1+ word chars followed 0+ sequences of ' or - followed 1+ word chars).

details

• ^ - start of string
• \w+ - 1 or more word chars
• \s+ - 1 or more whitespaces
• \w+ - 1 or more word chars
• # - #
• \w+\s+\w+ - see above
• $ - end of string.