Reputation: 1560
SELECT REGEXP_REPLACE (FIRST, '[^a-zA-Z0-9]+', '', 'g')
FROM example
WHERE LAST LIKE '%Belanger%'
'first' is a varchar2 field. I'm just trying to remove the non-alphanumeric characters from the 'first field.
Upvotes: 0
Views: 108
Reputation: 112342
where last LIKE 'Belanger'
only returns rows where last
equals "Belanger"
. Add appropriate wildcards
WHERE last LIKE '%Belanger%' -- contains 'Belanger'
or
WHERE last LIKE 'Belanger%' -- starts with 'Belanger'
or
WHERE last LIKE '%Belanger' -- ends with 'Belanger'
or (without LIKE)
WHERE last = 'Belanger' -- equals 'Belanger'
It also looks like the syntax for regexp_replace
is wrong. syntax:
REGEXP_REPLACE( string, pattern [, replacement_string [, start_position
[, nth_appearance [, match_parameter ] ] ] ] )
The last parameter is match_parameter
which has to be preceeded by start_position
and nth_appearance
. Valid match parameters are (according to TechNet REGEXP_REPLACE Function):
'c' Perform case-sensitive matching.
'i' Perform case-insensitive matching.
'n' Allows the period character (.) to match the newline character.
By default, the period is a wildcard.
'm' expression is assumed to have multiple lines, where ^ is the start of a line and $ is the end of
a line, regardless of the position of those characters in expression. By default, expression is assumed to be a single line.
'x' Whitespace characters are ignored. By default, whitespace characters are matched like any
other character.
However, this should work without this 'g'
parameter (whatever it was meant to do):
regexp_replace(first, '[^a-zA-Z0-9]+', '')
Upvotes: 1
Reputation: 16001
Try this (replace anything that is not in ranges a-z
or 0-9
with null, starting at position 1, all occurrences, case-insensitive):
regexp_replace(first, '[^a-z0-9]+', '', 1, 0, 'i')
or this (replace anything in the POSIX [:alnum:]
(alphanumeric) character class):
regexp_replace(first, '[^[:alnum:]]')
Upvotes: 0
Reputation: 91
You don't need 'g'
on this place. Following query is enough. Check it in documentation provided by @kfinity
SELECT regexp_replace(first, '[^a-zA-Z0-9]+', '')
FROM example
where last LIKE '%Belanger%'
Upvotes: 1