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.

score 4 · Accepted Answer · answered Feb 22 '15 at 09:17

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

Some other ways of achieving this: http://stackoverflow.com/questions/13240298/remove-numbers-from-string-sql-server — tourniquet_grab, Feb 22 '15 at 09:20

Lalit Kumar B · Answer 2 · 2015-02-22T10:51:39.707

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>

ERROR: function regexp_replace(text, unknown) does not exist — Luffydude, Jun 16 '21 at 16:50
@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. — Lalit Kumar B, Jun 18 '21 at 20:21

score 1 · Answer 3 · edited Feb 22 '15 at 10:35

1

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

edited Feb 22 '15 at 10:35

Lalit Kumar B

47,486
13
97
124

answered Feb 22 '15 at 09:12

ilj

859
8
18

It will always have a space on both sides, you need to trim the spaces. – Lalit Kumar B Feb 22 '15 at 10:55

Remove numbers found in string column

3 Answers3

Linked