CODEWITHSUNDEEP
sqloracle-database
Lorik Berisha
Lorik Berisha

Reputation: 263

SQL removing characters from a string

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

Upvotes: 2

Views: 677

Answers (2)

Joakim Danielson
Joakim Danielson

Reputation: 51871

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\]*', '')

Upvotes: 6

Gordon Linoff
Gordon Linoff

Reputation: 1269493

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

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

Upvotes: 3

Related Questions