Remove characters 1

[kernal]

Remove characters in a field in a select statement so I'm hoping this would just be a simple expression:

500
420
101F
204H
14A

I want all the data to just include the numbers so if data does include a letter then I want to remove it.

The letter could be any letter in the alphabet and it would always be at the end of the data.

Final results wanted:

500
420
101
204
14

Help is appreciated.

 

[SantaMufasa]

The following code should do what you want:

SELECT STR_IN FROM KERNAL;

STR_IN
-------
500
420
101F
204H
14A

select translate(UPPER(STR_IN),'^ABCDEFGHIJKLMNOPQRSTUVWXYZ','^') STR_IN from KERNAL;

STR_IN
------
500
420
101
204
14

The explanation of the code, above, in execution order is:

For each row from the KERNAL table,
[tt]
[ul][li]UPPER-case the column, STR_IN[/li]
[li]Check STR_IN for the existence of any of the characters,
'^ABCDEFGHIJKLMNOPQRSTUVWXYZ'
replaceing any character matches with its corresponding character(s) in this string:
'^',NULL,NULL,NULL...et cetera[/li][/ul][/tt]
Therefore, the above code simply "squeezes out" any alphabetic characters.

Let us know if this resolves your need.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[SantaMufasa]

(spell check, spell check, spell check..."replaceing" should be "replacing", obviously).

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[kernal]

Worked GREAT! Thanks for the quick reply to my problem.

 

[Dagon]

Another method, which might be a little less cumbersome is to use a regular expression:

select regexp_replace('xxxxxx101Fgggg', '[a-zA-Z]', '') from dual

101

For Oracle-related work, contact me through Linked-In.

 

[Dagon]

Although this is only available from 10g onwards, I think.

For Oracle-related work, contact me through Linked-In.