This content has been marked as final. Show 4 replies
your question is far too generic. What is junk value according to your definition?
As mentioned in SQL and PL/SQL FAQ I suggest to post sample data (CREATE TABLE and INSERT statement or WITH clause with sample data), explain better the logic and show your expected output.
I'm almost sure you can reach your goal using TRANSLATE or regular expressions but you need to clarify your requirements.
Thanks for the reply Alberto.
To be more specific, Im taking Student table and columns are ID,Name as example.
Junk can be anything like @,$,%,^ etc.. And that junk value can be in Id column or Name column. If its in Id column, my output should be COLUMN: ID and the value should be 01%67 / 76#78 etc..
And this should be done dynamically without hardcoding.
Hope you understand my Question now..
Try this, I'm not sure if it'll help... and get some idea...
try to modify it as per your requirement -
NOTE - I've considered anything other than Alphabets and Numbers as a Junk Value
select * from junk_vals; 1 ranit1 2# ranit2 3 ranit3 4$ ranit@4 5 ranit5 6 r^anit6 select 'ID', id, val from junk_vals where regexp_like(id,'[^a-zA-Z0-9]') UNION select 'VALUE', id, val from junk_vals where regexp_like(val,'[^a-zA-Z0-9]'); -- "Output :" ID 2# ranit2 ID 4$ ranit@4 VALUE 4$ ranit@4 VALUE 6 r^anit6
Edited by: ranit B on Jan 4, 2013 5:07 PM
-- o/p added