SQL REGEXP_LIKE Show data with special characters excluding spaces-CodePudding

I am trying to create a query where I want to find any string with a number shown below

REGEXP_LIKE (name, '[0-9]$')

I would also like to show the names that contain any special characters not including spaces (spaces are valid) I would also like this to be separate from the number example shown above.

Characters I want to check if in string

`~!@#$%^&*()_- =?/\|][{}"':;><.,

Below doesn't work

REGEXP_LIKE( name, '[!#$%&()* ,\-./:;<=>?@[\\\]^`{|}~]' )

However I can't seem to get it to work, any help would be great.

CodePudding user response：

You can use the translate command to remove characters.


CREATE TABLE t (name) AS
SELECT 'Hello World' FROM DUAL UNION ALL
SELECT 'Acx÷×' FROM DUAL UNION ALL
SELECT '123 456 789' FROM DUAL UNION ALL
SELECT '~!@#$%Xxv^&*()_ =\sv{}[]:”;’<,>./?' FROM DUAL

SELECT TRANSLATE(name, '~!@÷*#$%^&*()_ =\{}[]:”;’<,>./?',' ') as no_special_char
FROM t;

NO_SPECIAL_CHAR
Hello World
Acx×
123 456 789
 Xxvsv

CodePudding user response：

Using a WITH clause is a good way to set up test data and makes it real easy to try different test strings. Think of it like a temp table. Your first regexp_like() tests for a number at the end of the string. See below to test for a number anywhere in the string. Use the posix regex shorthand [[:punct:]] to match punctuation characters. I just combined all the examples into one query below.

with tbl(id, str) as (
  select 1, 'test' from dual union all
  select 2, 'test 1 test' from dual union all
  select 3, 'test 2' from dual union all
  select 4, '3 test' from dual union all
  select 5, '4 ` test' from dual union all
  select 6, 'test ~ test' from dual
)
select id, str
from tbl
where regexp_like(str, '[0-9]$') -- ends in a number
or regexp_like(str, '\d') -- contains a number anywhere
or regexp_like(str, '[[:punct:]]'); -- contains a punctuation character anywhere


        ID STR        
---------- -----------
         2 test 1 test
         3 test 2     
         4 3 test     
         5 4 ` test   
         6 test ~ test

5 rows selected.