Remove numbers found in string column

Question

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.

Solution 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', '' );

Solution 2

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

Solution 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>

Comments

[tourniquet_grab]

Some other ways of achieving this: stackoverflow.com/questions/13240298/…

[Lalit Kumar B]

It will always have a space on both sides, you need to trim the spaces.

[Luffydude]

ERROR: function regexp_replace(text, unknown) does not exist

[Lalit Kumar B]

@Luffydude That's not an Oracle error, any Oracle error starts with ORA- error code followed by error message. Post your use case with the Oracle version and explain what you are actually trying to do.