Remove numbers found in string column

问题:

What would be the SQL to remove all numbers found in an otherwise string column using Sqlite (an Oracle example would be appreciated too)?

Example : I would like to remove all numbers from entries like this :

291 HELP,1456 CALL

Expected output:

HELP,CALL

edit: I have edited the question because it is not only from one entry that I want to remove numbers but many of them.

回答1:

Either you do it in the language, you embedded sqlite, or you use this SQLite code, that removes all numbers:

UPDATE table SET column = replace(column, '0', '' );
UPDATE table SET column = replace(column, '1', '' );
UPDATE table SET column = replace(column, '2', '' );
UPDATE table SET column = replace(column, '3', '' );
UPDATE table SET column = replace(column, '4', '' );
UPDATE table SET column = replace(column, '5', '' );
UPDATE table SET column = replace(column, '6', '' );
UPDATE table SET column = replace(column, '7', '' );
UPDATE table SET column = replace(column, '8', '' );
UPDATE table SET column = replace(column, '9', '' );

回答2:

select '''' || regexp_replace('123 help 321', '\d+') || '''' from dual;

回答3:

Using TRANSLATE and REPLACE

SQL> WITH DATA AS(
  2  SELECT '291 HELP' str FROM dual UNION ALL
  3  SELECT '1456 CALL' str FROM dual
  4  )
  5  SELECT REPLACE(translate(str, '0123456789', ' '), ' ', NULL) str
  6  FROM DATA
  7  /

STR
---------
HELP
CALL

SQL>

Using REGEXP_REPLACE

SQL> WITH DATA AS(
  2  SELECT '291 HELP' str FROM dual UNION ALL
  3  SELECT '1456 CALL' str FROM dual
  4  )
  5  SELECT trim(regexp_replace(str, '[0-9]+')) str
  6  FROM DATA
  7  /

STR
---------
HELP
CALL

SQL>

POSIX character class

SQL> WITH DATA AS(
  2  SELECT '291 HELP' str FROM dual UNION ALL
  3  SELECT '1456 CALL' str FROM dual
  4  )
  5  SELECT trim(regexp_replace(str, '^[[:digit:]]+')) str
  6  FROM DATA
  7  /

STR
---------
HELP
CALL

SQL>

Perl-extensions

SQL> WITH DATA AS(
  2  SELECT '291 HELP' str FROM dual UNION ALL
  3  SELECT '1456 CALL' str FROM dual
  4  )
  5  SELECT trim(regexp_replace(str, '\d+')) str
  6  FROM DATA
  7  /

STR
---------
HELP
CALL

SQL>