SQL to extract substring from VARCHAR2

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
Mark_1976Asked:
Who is Participating?
 
GGuzdziolCommented:
SQL> select * from t;

T
-------------------------------------------------------------------------------------------------------------------

Account No.: 0025692
Account Type: DEALING
Fund: Cf Ruffer European Fund Acc
Symbol: 196448.SFU
ISIN: GB0001678161
Quantity: 2720.027
Channel: Internet

Account No.: 0020495
Account Type: MAXI_ISA
Fund: Invesco Perpetual Income Inc
Symbol: 187261.SFU
ISIN: GB0033053827
Quantity: 300.0
Channel: Internet


SQL> select substr(t, instr(t, chr(10), 1, 5) + 11, instr(t, chr(10), 1, 6) - instr(t, chr(10), 1, 5) - 11) from t;

SUBSTR(T,INSTR(T,CHR(10),1,5)+11,INSTR(T,CHR(10),1,6)-INSTR(T,CHR(10),1,5)-11)
-------------------------------------------------------------------------------------------------------------------

2720.027
300.0

HTH,
Grzegorz
0
 
Mark_1976Author Commented:
Thanks - that's done the trick.
0
 
GGuzdziolCommented:
Just curious - are You going to click Accept?
0
 
Tareq_AsebieCommented:
invoice
-------------
   Qty
-------------
  150.023
 1508.023

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

 Result is :
-------
150.0
1508.0
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.