help with TRANSLATE function [message #230759] |
Thu, 12 April 2007 13:38  |
staann56
Messages: 136 Registered: May 2006 Location: atlanta
|
Senior Member |
|
|
I'm using the TRANSLATE function to return only numbers in a column. However, I came across an anomaly that I'm
not sure how to translate. The value in the column is
144 EA MISC & 144 EA MISC
I only want to return 144 and with my TRANSLATE command I'm getting 144144. What can I do to prevent this?
Here's my TRANSLATE command:
TRANSLATE(value, ' "&"EA MISCabcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ/`<>?!@#$%^&*()_+-=', '$')
Thanks for looking,
Stan
|
|
|
|
|
|
Re: help with TRANSLATE function [message #230775 is a reply to message #230768] |
Thu, 12 April 2007 14:49   |
staann56
Messages: 136 Registered: May 2006 Location: atlanta
|
Senior Member |
|
|
Based on your statement:
Quote: |
First don't remove space then remove all what is after the first space
|
There is no space between the last number and the first letter in this example:
178mg/dl Fssv
What I'm wanting to achieve is spelled out in my original post.....I'm wanting to parse out the numbers out of an alphanumeric field. This is what's in my table:
144 EA MISC & 144 EA MISC
178mg/dl Fssv
11mg/dl Fssv
123mg/dl Fssv
3mg/dl Fssv
112 EA MISC & 1 EA MISC
234 EA MISC
I want to report:
144
178
11
123
3
112
234
I'll look at your suggestion again. I must be missing something.
Thanks,
Paul
|
|
|
Re: help with TRANSLATE function [message #230786 is a reply to message #230775] |
Thu, 12 April 2007 16:54   |
SnippetyJoe
Messages: 63 Registered: March 2007 Location: Toronto, Canada
|
Member |
|
|
If you're running on Oracle 10g you can try REGEXP_SUBSTR instead of TRANSLATE.
set scan off
set feedback off
create table t ( c varchar2(25) );
insert into t values ( '144 EA MISC & 144 EA MISC' );
insert into t values ( '178mg/dl Fssv' );
insert into t values ( '11mg/dl Fssv' );
insert into t values ( '123mg/dl Fssv' );
insert into t values ( '3mg/dl Fssv' );
insert into t values ( '112 EA MISC & 1 EA MISC' );
insert into t values ( '234 EA MISC' );
column c_digits format a25
select c, regexp_substr( c, '[[:digit:]]+' ) c_digits from t ;
C C_DIGITS
------------------------- -------------------------
144 EA MISC & 144 EA MISC 144
178mg/dl Fssv 178
11mg/dl Fssv 11
123mg/dl Fssv 123
3mg/dl Fssv 3
112 EA MISC & 1 EA MISC 112
234 EA MISC 234
--
Joe Fuda
http://www.sqlsnippets.com/
|
|
|
|
|
|
|
|
|
|
|
Re: help with TRANSLATE function [message #230845 is a reply to message #230841] |
Fri, 13 April 2007 01:57   |
pavuluri
Messages: 247 Registered: January 2007
|
Senior Member |
|
|
hi
guys see this query
select substr(num,1,instr(num,',')-1) from(
select replace(rama123,'___' , ',') as num from
(SELECT TRANSLATE('123 asds aass 11', ' ''abcdfghijklmnopqrst', '___') as rama123 FROM DUAL))
thanks
srinivas
|
|
|
|
Re: help with TRANSLATE function [message #230936 is a reply to message #230841] |
Fri, 13 April 2007 08:11  |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
Frank wrote on Fri, 13 April 2007 02:23 | And please stop kidnapping threads.
You seem to take over all other people's problems.
|
And after 186 posts you STILL don't know how to use code tags to make code readable? Why are you so goddamn lazy?
|
|
|