Get particular data using decode(urgent) [message #364947] |
Wed, 10 December 2008 09:52 |
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 |
|
Michel Cadot
Messages: 68712 Registered: March 2007 Location: Saint-Maur, France, https...
|
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 |
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;
|
|
|
|
|
|
|