How do I extract a part of a string and cast it into an integer?

Question

For Example - If I have a string as mentioned below :-

Summary: Start Volume (monthly) = 2 End Volume (monthly) = 0 Volume Reduction (year) = 13 Efficiency Gains = 100 Log: 09/13/2017

I just want to show the value of Volume Reduction in a column. What query should I write?

could you explain more exactly what you're going to approach? which the RDBMS you're using? — Esteban P., Sep 13 '17 at 06:30
I am trying to use this in a OBIEE Tool. I want to write a sql query for that. — Sanjukta Ghosh, Sep 13 '17 at 06:33
If you just want to show the Volume Reduction, just write "SELECT VolumeReduction FROM yourtable" — Esteban P., Sep 13 '17 at 06:34
please read: "How to write a SELECT query" https://www.w3schools.com/sql/sql_select.asp — Esteban P., Sep 13 '17 at 06:39
I can't Select VolumeReduction From table. I am trying to read the value from a string present in the column. — Sanjukta Ghosh, Sep 13 '17 at 06:40
@SanjuktaGhosh please specify the string from which you want to read the data or value . — Yogesh Sharma, Sep 13 '17 at 06:40
I have already given the string in the example. Please check above — Sanjukta Ghosh, Sep 13 '17 at 06:42
"Summary: Start Volume (monthly) = 2 End Volume (monthly) = 0 Volume Reduction (year) = 13 Efficiency Gains = 100 Log: 09/13/2017" This is the string guys. — Sanjukta Ghosh, Sep 13 '17 at 06:42
In OBIEE one does NOT "write SQL" but you work with models. If you start writing SQL you're already using the tool totally wrong and better should be using something else...like Notepad. — Chris, Sep 13 '17 at 15:31

score 0 · Answer 1 · answered Sep 13 '17 at 06:50

Try this one

SELECT SUBSTR(COL,S1,INSTR(COL,' ',S1)-S1) X
FROM  (SELECT COL, INSTR( COL, 'Volume Reduction (year) = ',1)+LEN('Volume Reduction (year) = ') AS S1
       FROM (SELECT 'Summary: Start Volume (monthly) = 2 End Volume (monthly) = 0 Volume Reduction (year) = 13 Efficiency Gains = 100 Log: 09/13/2017' AS COL 
             FROM DUAL) A
        ) B;

Output:

X
13

score 0 · Answer 2 · answered Sep 13 '17 at 06:58

Try this one :

DECLARE @DATA VARCHAR(256);
DECLARE @DATA1 VARCHAR(256);

SET @DATA = 'Summary: Start Volume (monthly) = 2 End Volume (monthly) = 0 Volume Reduction (year) = 13 Efficiency Gains = 100 Log: 09/13/2017'

SELECT @DATA1 = RIGHT(@DATA,LEN(@DATA)-CHARINDEX(' 13',@DATA))

SELECT SUBSTRING(@DATA1, PATINDEX('%[1-9]%', @DATA1), LEN(120)) AS DATA

How do I extract a part of a string and cast it into an integer?

2 Answers2