Home » SQL & PL/SQL » SQL & PL/SQL » Read only the input string from BLOB datatype column (Oracle 10G)
Read only the input string from BLOB datatype column [message #580780] Thu, 28 March 2013 01:37 Go to next message
Gana13
Messages: 9
Registered: November 2008
Junior Member
I have a table that has column with BLOB datatype. I am trying read only the inputted String from the BLOB datatype column. I have used the below query,
SELECT utl_raw.cast_to_varchar2(column1) FROM TAB1
and it gives the result as
<H2><FONT color=#cc0000><EM><U>test</U></EM></FONT></H2>

However I would like to extract only the string "test" which is inputted. Is there any build-in function in oracle which will satisfy this requirement?
Re: Read only the input string from BLOB datatype column [message #580781 is a reply to message #580780] Thu, 28 March 2013 01:46 Go to previous messageGo to next message
Michel Cadot
Messages: 58938
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Here's a hint:
SQL> select extractvalue(xmltype('<H2><FONT color="#cc0000"><EM><U>test</U></EM></FONT></H2>'),'//U') res
  2  from dual;
RES
--------
test

Regards
Michel
Re: Read only the input string from BLOB datatype column [message #580792 is a reply to message #580781] Thu, 28 March 2013 04:46 Go to previous messageGo to next message
Gana13
Messages: 9
Registered: November 2008
Junior Member
Hi Michel,
Thanks for the info.! However, the column1 will have records like detailed below. Can you help me in amending your query for this.

Column1
--------------
<H1><FONT color=#660000>tset</FONT></H1>
test
<U><FONT color=#cc0000><STRONG><EM>test</EM></STRONG></FONT></U>
test
<STRONG><EM><U><FONT color=#cc0000>testing</FONT></U></EM></STRONG>
test
<FONT color=#cc6600><STRONG><EM><U>test</U></EM></STRONG></FONT>

I have tried to use INSTR & SUBSTR and couldn't get the successful results. Once again thanks for your help.!
Re: Read only the input string from BLOB datatype column [message #580796 is a reply to message #580792] Thu, 28 March 2013 05:21 Go to previous messageGo to next message
Michel Cadot
Messages: 58938
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to clear specify what can be the data and what has to be retrieve.
I will surely not provide another solution for you to answer again: "However, the column1 will have records like detailed below".
Once all is specified then we can start to work.

Regards
Michel
Re: Read only the input string from BLOB datatype column [message #580801 is a reply to message #580796] Thu, 28 March 2013 05:31 Go to previous messageGo to next message
Gana13
Messages: 9
Registered: November 2008
Junior Member
As mentioned the column1 is BLOB datatype, and it can have the PLAIN text & FORMATTED text as well. In FORMATTED text format, only the FONT can be applied, or only the SIZE can be applied, or only the BOLD/ITALIC can be applied, or any combination of FONT/SIZE/STYLE can be applied. Hence the column can have data like mentioned below. Here plain text is shown as "test" and formatted text of "test" is shown in respective format syle. The text is not limited to "test" and it can be any long string value.

Column1
--------------
<H1><FONT color=#660000>tset</FONT></H1>
test
<U><FONT color=#cc0000><STRONG><EM>test</EM></STRONG></FONT></U>
test
<STRONG><EM><U><FONT color=#cc0000>testing</FONT></U></EM></STRONG>
test
<FONT color=#cc6600><STRONG><EM><U>test</U></EM></STRONG></FONT>
Re: Read only the input string from BLOB datatype column [message #580803 is a reply to message #580801] Thu, 28 March 2013 05:34 Go to previous messageGo to next message
Michel Cadot
Messages: 58938
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What should be the result from what you posted?
Is what you posted a lone record or several?

Regards
Michel
Re: Read only the input string from BLOB datatype column [message #580806 is a reply to message #580803] Thu, 28 March 2013 05:41 Go to previous messageGo to next message
Gana13
Messages: 9
Registered: November 2008
Junior Member
I have posted 6 row data in the previous post. Please find the output of the "SELECT utl_raw.cast_to_varchar2(COLUMN1), ROWNUM FROM TAB1" query as mentioned below. It has 61 rows as of now.

I would like to retrive only the TEXT which is been inputted from this column, like tset, test , test gashour, tests, test data, TEST, test whitwell..


UTL_RAW.CAST_TO_VARCHAR2(COLUMN1) ROWNUM
<H1><FONT color=#660000>tset</FONT></H1> 1
rwar 2
test 3
test gashour 4
test 5
test 6
test 7
test 8
test 9
test 10
<H2><FONT color=#cc0000><EM><U>test</U></EM></FONT></H2> 11
test 12
tewt 13
tests 14
test 15
test 16
<STRONG><EM><U><FONT color=#cc0000>testing</FONT></U></EM></STRONG> 17
test 18
<P><STRONG>test</STRONG></P> 19
testing first 2hr 20
<FONT color=#996633 size=4><STRONG><EM><U>test data</U></EM></STRONG></FONT> 21
test 22
test by madhu 23
test 24
test 25
<STRONG><FONT color=#660000>testing</FONT></STRONG> 26
test 27
test 28
test 29
test 30
test 31
test 32
test 33
t<FONT color=#cc0000>est</FONT> 34
<FONT color=#660000 size=4><STRONG><EM><U>testing</U></EM></STRONG></FONT> 35
test 36
<STRONG><EM><U><FONT color=#cc0000 size=4>testing</FONT></U></EM></STRONG> 37
test 38
test 39
test 40
test data 41
test whitwell 42
test 43
test 44
test 45
<STRONG><EM><FONT color=#ff9900>test</FONT></EM></STRONG> 46
tetse 47
<STRONG><FONT color=#cc0000><EM><U>test</U></EM></FONT></STRONG> 48
<STRONG><U>test</U></STRONG> 49
<STRONG><EM><U><FONT color=#660000>test data</FONT></U></EM></STRONG> 50
<U><FONT color=#cc0000><STRONG><EM>test</EM></STRONG></FONT></U> 51
<FONT color=#cc6600><STRONG><EM><U>test</U></EM></STRONG></FONT> 52
TEST 53
testdata 54
test 55
<FONT color=#990000><EM><U>test</U></EM></FONT> 56
test 57
ttest 58
test 59
tet 60
<STRONG><FONT color=#660000 size=5><EM><U>test data</U></EM></FONT></STRONG> 61
Re: Read only the input string from BLOB datatype column [message #580824 is a reply to message #580806] Thu, 28 March 2013 07:14 Go to previous message
_jum
Messages: 490
Registered: February 2008
Senior Member
If you want to use XML functions, your string has to be well formed without syntax errrors.
I modified the string slightly:
WITH xdata AS
  (SELECT XMLTYPE('
<XMLT>
<H1><FONT color="#660000">tset</FONT></H1> 1
rwar 2
test 3
test gashour 4
test 5
test 6
test 7
test 8
test 9
test 10
<H2><FONT color="#cc0000"><EM><U>test</U></EM></FONT></H2> 11
test 12
tewt 13
tests 14
test 15
test 16
<STRONG><EM><U><FONT color="#cc0000">testing</FONT></U></EM></STRONG> 17
test 18
<P><STRONG>test</STRONG></P> 19
testing first 2hr 20
<FONT color="#996633" size="4"><STRONG><EM><U>test data</U></EM></STRONG></FONT> 21
test 22
test by madhu 23
test 24
test 25
<STRONG><FONT color="#660000">testing</FONT></STRONG> 26
test 27
test 28
test 29
test 30
test 31
test 32
test 33
t<FONT color="#cc0000">est</FONT> 34
<FONT color="#660000" size="4"><STRONG><EM><U>testing</U></EM></STRONG></FONT> 35
test 36
<STRONG><EM><U><FONT color="#cc0000" size="4">testing</FONT></U></EM></STRONG> 37
test 38
test 39
test 40
test data 41
test whitwell 42
test 43
test 44
test 45
<STRONG><EM><FONT color="#ff9900">test</FONT></EM></STRONG> 46
tetse 47
<STRONG><FONT color="#cc0000"><EM><U>test</U></EM></FONT></STRONG> 48
<STRONG><U>test</U></STRONG> 49
<STRONG><EM><U><FONT color="#660000">test data</FONT></U></EM></STRONG> 50
<U><FONT color="#cc0000"><STRONG><EM>test</EM></STRONG></FONT></U> 51
<FONT color="#cc6600"><STRONG><EM><U>test</U></EM></STRONG></FONT> 52
TEST 53
testdata 54
test 55
<FONT color="#990000"><EM><U>test</U></EM></FONT> 56
test 57
ttest 58
test 59
tet 60
<STRONG><FONT color="#660000" size="5"><EM><U>test data</U></EM></FONT></STRONG>
</XMLT>') xd FROM dual)
SELECT  col_pos, tag, val FROM xdata, 
  XMLTABLE( '/XMLT'
  --XMLTABLE( '/XMLT/*'
    PASSING xdata.xd
    COLUMNS tag VARCHAR2(20) path 'name()',
            col_pos FOR ORDINALITY,
            val VARCHAR2(1000) path '.');
COL_PAS TAG      VAL
-------------------------    
1	XMLT	 tset 1 rwar 2 test 3 test gashour 4 ... tet 60 test data


You can modify the XMLTABLE function to get the wanted output.
Previous Topic: Need a output from multilple rows return only one row in oracle?
Next Topic: row to column
Goto Forum:
  


Current Time: Sat Aug 30 08:12:18 CDT 2014

Total time taken to generate the page: 0.05766 seconds