• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1023
  • Last Modified:

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
0
Mark_1976
Asked:
Mark_1976
  • 2
1 Solution
 
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

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now