<div>
Thanks - that's done the trick.
</div>


<div>
Just curious - are You going to click Accept?
</div>


Just curious - are You going to click Accept?

<div>
 invoice <br />
-------------<br />
&nbsp; &nbsp;Qty<br />
-------------<br />
&nbsp; 150.023<br />
&nbsp;1508.023<br />
<br />
select substr(Qty,1,instr(Qty,'.'<wbr />,1)+1) from invoice<br />
<br />
&nbsp;Result is : <br />
-------<br />
150.0<br />
1508.0
</div>


invoice
-------------
   Qty
-------------
  150.023
 1508.023

select substr(Qty,1,instr(Qty,'.',1)+1) from invoice

 Result is :
-------
150.0
1508.0

<div>
<div class="content wysiwyg-content">
Hi,<br />
<br />
I'm hoping someone can help. I have a VARCHAR2 field from which I need to extract a specific peice of the string. The problem is that this is not always in the same place in the string, e.g. I want to extract 300.0 from here:<br />
<br />
Account No.: 0020495<br />
Account Type: MAXI_ISA<br />
Fund: Invesco Perpetual Income Inc<br />
Symbol: 187261.SFU<br />
ISIN: GB0033053827<br />
Quantity: 300.0<br />
Channel: Internet<br />
<br />
but sometimes the value may start at a different location, and be a different length, like so<br />
<br />
Account No.: 0025692<br />
Account Type: DEALING<br />
Fund: Cf Ruffer European Fund Acc<br />
Symbol: 196448.SFU<br />
ISIN: GB0001678161<br />
Quantity: 2720.027<br />
Channel: Internet<br />
<br />
Can anyone help?<br />
<br />
Thanks<br />
<br />
Mark
</div>
</div>


Hi,

I'm hoping someone can help. I have a VARCHAR2 field from which I need to extract a specific peice of the string. The problem is that this is not always in the same place in the string, e.g. I want to extract 300.0 from here:

Account No.: 0020495
Account Type: MAXI_ISA
Fund: Invesco Perpetual Income Inc
Symbol: 187261.SFU
ISIN: GB0033053827
Quantity: 300.0
Channel: Internet

but sometimes the value may start at a different location, and be a different length, like so

Account No.: 0025692
Account Type: DEALING
Fund: Cf Ruffer European Fund Acc
Symbol: 196448.SFU
ISIN: GB0001678161
Quantity: 2720.027
Channel: Internet

Can anyone help?

Thanks

Mark

SQL to extract substring from VARCHAR2

Oracle is an object-relational database management system. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java, but it also includes support for C and C++. Oracle also has its own enterprise modules and application server software.