Home » SQL & PL/SQL » SQL & PL/SQL » Get particular data using decode(urgent)
Get particular data using decode(urgent) [message #364947] Wed, 10 December 2008 09:52 Go to next message
shrad2001
Messages: 20
Registered: September 2008
Location: faridabad
Junior Member
Hi
I have table
Example Dtest having column unit values as given below
unit
rs10/ grams
rs20/ kwintal
rs39/kg
Kilogram

I want to write command that get only units from this column like kwintal,kg
table have 1000 of data
so i couldnt write decode for every data becoz i dont know data used by clients
i have wrote the command
Select DECODE(unit,
'Rs10/ Grams', 'Grams',
'Rs10/ kg', 'kg',
'Rs10/ kwintal', 'kwintal',
' ') from dtest
/

it works well if i know the values but i dont know the values

help me
thanks
smily
Re: Get particular data using decode(urgent) [message #364954 is a reply to message #364947] Wed, 10 December 2008 10:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you post:
- in the wrong forum
- in the wrong way (IM speak, no format, no test case, no try, use "urgent"...)
you will likely have few if not no answer.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Post a test case: create table and insert statements along with the result you want with these data.

Use SQL*Plus and copy and paste what you already tried.

Regards
Michel

[Updated on: Wed, 10 December 2008 10:25]

Report message to a moderator

Re: Get particular data using decode(urgent) [message #364960 is a reply to message #364947] Wed, 10 December 2008 11:18 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
To paraphrase your problem: You want to extract part of a column from your table, but you don't know the format of the data, or the range of values that you have to deal with.

Let us know how you get on with that.


Based on the very limited data you've provided, this might solve your problem:
with src as (select 'rs10/ grams' col_1 from dual union all
             select 'rs20/ kwintal' from dual union all
             select 'rs39/kg' from dual union all
             select 'Kilogram' from dual)
select regexp_substr(col_1,'[^/]+$')
from src;
Re: Get particular data using decode(urgent) [message #365084 is a reply to message #364947] Wed, 10 December 2008 22:08 Go to previous messageGo to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member
use INSTR
Re: Get particular data using decode(urgent) [message #365264 is a reply to message #365084] Thu, 11 December 2008 04:32 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Instr returns the position of string A in string B.

How exactly is that going to help?

INSTR and SUBSTR togerther might work, but INSTR by itself isn't going to do it.
Re: Get particular data using decode(urgent) [message #366272 is a reply to message #364947] Fri, 12 December 2008 09:48 Go to previous messageGo to next message
c_stenersen
Messages: 255
Registered: August 2007
Senior Member
Does this give you what you need?
(gives you the text after the last space in the column)
select substr(unit, instr(unit, ' ', -1)+1) only_unit 
from dtest
Re: Get particular data using decode(urgent) [message #366276 is a reply to message #364947] Fri, 12 December 2008 10:28 Go to previous message
knicely87
Messages: 25
Registered: December 2008
Location: Pittsburgh, PA
Junior Member
I think this will work:

select trim(substr(unit, instr(unit, '/', -1)+1)) only_unit ...


Thanks!
Previous Topic: typical query
Next Topic: Problem with date in bind variable
Goto Forum:
  


Current Time: Sun Dec 11 06:12:14 CST 2016

Total time taken to generate the page: 0.06783 seconds