KinsDotNet
KinsDotNet

Reputation: 1560

Why am I getting a invalid number from a varchar field on which I'm running regexp_replace?

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

Answers (3)

Olivier Jacot-Descombes
Olivier Jacot-Descombes

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

William Robertson
William Robertson

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

mmp
mmp

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

Related Questions