Home » SQL & PL/SQL » SQL & PL/SQL » help with TRANSLATE function
help with TRANSLATE function [message #230759] Thu, 12 April 2007 13:38 Go to next message
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 #230760 is a reply to message #230759] Thu, 12 April 2007 13:42 Go to previous messageGo to next message
Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
First don't remove space then remove all what is after the first space (clue: substr, instr).

Regards
Michel
Re: help with TRANSLATE function [message #230764 is a reply to message #230760] Thu, 12 April 2007 14:03 Go to previous messageGo to next message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
this approach wouldn't work because most values are like so:

178mg/dl Fssv

No space between last number and first character. And there can be 1 to 4 digits before the

mg/dl Fssv or
EA MISC



Thanks,
Stan

[Updated on: Thu, 12 April 2007 14:11]

Report message to a moderator

Re: help with TRANSLATE function [message #230768 is a reply to message #230764] Thu, 12 April 2007 14:30 Go to previous messageGo to next message
Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
What I posted works for your 2 exemples.

Finally what do you want to achieve?
What is your real problem?
If there is several numbers, which one you take? Why?

Regards
Michel

[Updated on: Thu, 12 April 2007 14:32]

Report message to a moderator

Re: help with TRANSLATE function [message #230775 is a reply to message #230768] Thu, 12 April 2007 14:49 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #230789 is a reply to message #230786] Thu, 12 April 2007 18:24 Go to previous messageGo to next message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
Unfortunately, I'm running 9i.
Re: help with TRANSLATE function [message #230808 is a reply to message #230789] Thu, 12 April 2007 23:22 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
First translate non-numerics to space, then do the SUBSTR.

Ross Leishman
Re: help with TRANSLATE function [message #230811 is a reply to message #230808] Thu, 12 April 2007 23:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Thanks Ross to TRANSLATE what i meant. Smile

Regards
Michel
Re: help with TRANSLATE function [message #230822 is a reply to message #230811] Fri, 13 April 2007 00:47 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
what is perfect query.
thanks
srinivas
Re: help with TRANSLATE function [message #230823 is a reply to message #230822] Fri, 13 April 2007 00:49 Go to previous messageGo to next message
Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
There is no perfect query till you don't answer to my questions:
Quote:
Finally what do you want to achieve?
What is your real problem?
If there is several numbers, which one you take? Why?


Did you try the modifications Ross and I posted you?

Regards
Michel
Re: help with TRANSLATE function [message #230825 is a reply to message #230823] Fri, 13 April 2007 00:54 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
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

is it clear
Re: help with TRANSLATE function [message #230839 is a reply to message #230825] Fri, 13 April 2007 01:18 Go to previous messageGo to next message
Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
No!
Exemple may be interpreted.
Say it with words.

For instance: 144 in the first line result, is it the first 144 or the second 144? (this was my last question)

Knowing what is the real problem may lead to a different approach.

And finally I think what Ross and I posted works perfectly, JUST TRY IT.

Regards
Michel
Re: help with TRANSLATE function [message #230841 is a reply to message #230839] Fri, 13 April 2007 01:23 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
And please stop kidnapping threads.
You seem to take over all other people's problems.
Re: help with TRANSLATE function [message #230845 is a reply to message #230841] Fri, 13 April 2007 01:57 Go to previous messageGo to next message
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 #230847 is a reply to message #230845] Fri, 13 April 2007 02:02 Go to previous messageGo to next message
Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Something like this but start with your first translate and strictly modified it with what we said.

Regards
Michel

[Updated on: Fri, 13 April 2007 08:29]

Report message to a moderator

Re: help with TRANSLATE function [message #230936 is a reply to message #230841] Fri, 13 April 2007 08:11 Go to previous message
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?
Previous Topic: ORA error with object no longer exists
Next Topic: More information about the views
Goto Forum:
  


Current Time: Wed May 28 17:25:20 CDT 2025