SQL removing characters from a string

Question

I have a column in a datawarehouse task which needs replacing these characters: "ABCDEFGHIJKLMNOPQRSTUVWXYZ\abcdefghijklmnopqrstuvwxyz" with nothing.

For example I have this form of data "88k77.22" and it should be "8877.22"

Does anyone know any particular function which can do this, or any workaround.

Thanks in advance

Replace and Regex_replace

Maciej Los
– Maciej Los

2018-12-07 10:19:37 +00:00
Commented Dec 7, 2018 at 10:19 — Maciej Los
– Maciej Los, Commented Dec 7, 2018 at 10:19
Replace with nothing, not with space... ?

GolezTrol
– GolezTrol

2018-12-07 10:19:55 +00:00
Commented Dec 7, 2018 at 10:19 — GolezTrol
– GolezTrol, Commented Dec 7, 2018 at 10:19
@HoneyBadger sorry, miss typo! I've edited it now

Lorik Berisha
– Lorik Berisha

2018-12-07 12:06:03 +00:00
Commented Dec 7, 2018 at 12:06 — Lorik Berisha
– Lorik Berisha, Commented Dec 7, 2018 at 12:06

Nikhil · Accepted Answer · 2018-12-07 11:50:17Z

6

Use a regular expression

REGEXP_REPLACE(column, '[A-Za-z]*', '')

Is '\' supposed to be included as well? Then use

 REGEXP_REPLACE(column, '[A-Za-z\]*', '')

edited Dec 7, 2018 at 11:50

Nikhil

3,9785 gold badges20 silver badges34 bronze badges

answered Dec 7, 2018 at 10:23

Joakim Danielson

53.1k5 gold badges36 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Derviş Kayımbaşıoğlu Over a year ago

\ is an escape character and it needs double slash to be escaped right?

Joakim Danielson Over a year ago

@Simonare, not when it is inside a pair of brackets. \s matches whitespace but [\s] matches characters 's' and '\'

Gordon Linoff · Accepted Answer · 2018-12-07 11:47:31Z

3

Oracle supports translate(), which does exactly what you want:

translate(col, ' ABCDEFGHIJKLMNOPQRSTUVWXYZ\abcdefghijklmnopqrstuvwxyz', ' ')

answered Dec 7, 2018 at 11:47

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

SQL removing characters from a string

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related