Home > Default > Parsing Column Value. SQL / Stored Proc/ Function ?

Parsing Column Value. SQL / Stored Proc/ Function ?

October 11Hits:1
Advertisement
Hi,
I just started writing SQL. Need your valuable input for the following query,
Need to query a table and parse a column to produce a desired output.
like suppose my column value is
#ADFA
/SDFGAS
{ABC}: 123
{BCA}: 456
{DEF}: 789
and i need to get an out put as follows
{ABC} {BCA} {DEF}
123 456 789
so the patterns are defined.
I some how tried and reached through SQL, but there should be better way than this. So posted this question.
I tried using SQL itself (using substr, instr & decode functions in the select statement)... with out using stored proc/ function as i have not created one earlier.. I am just learning the syntax and trying it.
The psuedo code i have planned is
function substring(column_value, search_string)
var start_index NUMBER;
var end_index NUMBER;
var result VARCHAR2;
result = 'N/A';
start_index = instr(column_value, search_string) + 4
if start_index >= 4
end_index = instr(substr(column_value, 0, start_index), CHR(13)-1)
if start_index >= 4 && end_index >=0
result = substr(column_value, start_index, end_index);
return result;
I just wrote it like writing in a programming language. Need to implement this through Stored Proc/ Function. Please let me know your comment on this

Answers

Oops, I think regular expressions are not available in my current oracle version :-(
I am having Oracle 9i.
Got it.. Yes it is available from 10G onwards.. :-(
Message was edited by:
Thiru.Thiru

Read other 5 answers

Tags:

Related Articles

  • Parsing Column Value. SQL / Stored Proc/ Function ?October 11

    Hi, I just started writing SQL. Need your valuable input for the following query, Need to query a table and parse a column to produce a desired output. like suppose my column value is #ADFA /SDFGAS {ABC}: 123 {BCA}: 456 {DEF}: 789 and i need to get a

  • Can we call a Java Stored Proc from a PL/SQL stored Proc?October 11

    Hello! Do you know how to call a Java Stored Proc from a PL/SQL stored Proc? is it possible? Could you give me an exemple? If yes, in that java stored proc, can we do a call to an EJB running in a remote iAS ? Thank you!For the java stored proc calle

  • Cf caching ms sql stored procs,November 30

    I'm having trouble with Cf caching ms sql stored procs, eg in this one:   <cfstoredproc  procedure="getUser"   datasource="#datasource#" returncode="true">       <cfprocparam type="in"  cfsqltype="CF_S

  • BPEL process to Insert XML into DB using pl/sql stored procNovember 30

    Hi, We have created a BPEL process in which we are taking input request and store it into Oracle Database using PL/SQL proc. We have been unsuccessful in doing so because the data stored in table has root element missing. Has any body done this (inse

  • Item Type Based on PL/SQL stored proc causing errorsNovember 30

    I am getting the following error when I add this item type based on a pl/sql stored proc that generates html based on the stored proc... the stored proc returns find but the other porlets get Error Message in there headers and in the porlet content I

  • URGENT!! Generating XML in a PL/SQL stored procNovember 30

    Hi, I need to generate XML from a PL/SQL stored proc. I need to do it in Oracle 7.3.4 as well as 8.1.6 databases. I need to have my own defined tags in the xml. Say I have the table Person like Person ( id number, fname varchar2(40), lname varchar2(4

  • Arrays to pl/sql stored proc.November 30

    How can I send a java array to a pl/sql stored proc. I've understood that I have to something like this ArrayDescriptor desc = ArrayDescriptor.createDescriptor(<sql_type_name>, conn); ARRAY newArray = new ARRAY(desc, conn, java_array); But what do I

  • Save prompted sql stored proc parm values in rpt file from designerNovember 30

    How can I save the last prompted sql stored proc parameter values from within the CR designer.  In other words I want to hard code some of the parameter values (the rest are passed) so that CR does not prompt at run time.  I am running CR Developer X

  • Parameter index move while executing PL/SQL stored procedure/functionOctober 11

    Hello, community. Have a question for you. It looked like very easy to write some small JDBC-wrapper to handle stored procedure/functions call for Oracle. Here is the code snippet of it: import java.io.Serializable; import java.sql.CallableStatement;

  • Passing parameters from Excel to SQL stored proc. to analyse resultset in PowerPivotNovember 30

    Hi, Not sure if I posted this question at the right forum ... I would like to implement the following scenario: - Enter parameters @startdate and @enddate in cells in an Excel worksheet (i.e. cell A2 has the value for the startdate parameter; cell B2

Copyright (C) 2019 wisumpire.com, All Rights Reserved. webmaster#wisumpire.com 14 q. 0.638 s.