Extracting data from the middle of a string in SQL Query

Question

I currently have a SQL query that is displaying several columns of data. In one of those columns I would like to pare down the data to only display what is really needed. The problem is the data I want is in the middle of string. I know what is before the value I need and what is after that is a fixed constant. The rest of the string may change. Is there a way in a SQL query to only display that data?

Below is an example of the data in the string. I would like to only see the data between the LR and SCCM.

A   %VR1  P  P%V 104.922043 %      Pt 4.902552 psig       PLR2  P  LR **0.350922** sccm      PL 0.001313 dpsig   

R   %VR1  P  P%V 105.017807 %      Pt 4.934245 psig       PLR2  F  LR **5.366095** sccm      PL 0.014557 dpsig     

A   %VR1  P  P%V 100.644852 %      Pt 5.112900 psig       PLR2  P  LR **-0.029057** sccm     PL 0.000377 dpsig     

A   %VR1  P  P%V 101.631256 %      Pt 5.107606 psig       PLR2  P  LR **2.515130** sccm      PL 0.006896 dpsig

Duplicate: http://stackoverflow.com/questions/2647/split-string-in-sql — Gaff, Oct 04 '12 at 15:55

score 1 · Answer 1 · answered Oct 04 '12 at 15:52

1

SQL engines have SUBSTR() or SUBSTRING() functions, which will do what you need if you will always need to skip the 2 first characters and the 4 last ones.

answered Oct 04 '12 at 15:52

mbarthelemy

12,465
4
41
43

score 1 · Answer 2 · answered Oct 04 '12 at 15:58

If you are using SQL Server

declare @String varchar(500)

set @String = 'A %VR1 P P%V 101.631256 % Pt 5.107606 psig PLR2 P LR 2.515130 sccm PL 0.006896 dpsig '

select SUBSTRING(@String, charindex('PLR2 P LR ', @String) + 10, charindex(' sccm PL', @String) - charindex('PLR2 P LR ', @String) - 10)

If you are sure about the LR and sccm being unique you could use:

declare @String varchar(500)

set @String = 'A %VR1 P P%V 101.631256 % Pt 5.107606 psig PLR2 P LR 2.515130 sccm PL 0.006896 dpsig '

select SUBSTRING(@String, charindex('LR ', @String) + 3, charindex(' sccm', @String) - charindex('LR ', @String) - 3)

Kermit · Accepted Answer · 2012-10-04T17:25:16.240

1

MySQL Version:

SET @var = 'A %VR1 P P%V 104.922043 % Pt 4.902552 psig PLR2 P LR 0.350922 sccm PL 0.001313 dpsig';

SELECT SUBSTRING(@var, LOCATE('LR ', @var) + 3, LOCATE('sccm', @var) - LOCATE('LR ', @var) - 3)

Result

0.350922

SQL Server Version:

DECLARE @var varchar(200)
SET @var = 'A %VR1 P P%V 104.922043 % Pt 4.902552 psig PLR2 P LR 0.350922 sccm PL 0.001313 dpsig'

SELECT SUBSTRING(@var, CHARINDEX('LR ', @var) + 3, CHARINDEX('sccm', @var) - CHARINDEX('LR ', @var) - 3)

Result

0.350922

With a column name:

SELECT SUBSTRING(col1, CHARINDEX('LR ', col1) + 3, CHARINDEX('sccm', col1) - CHARINDEX('LR ', col1) - 3)

edited Oct 04 '12 at 17:25

answered Oct 04 '12 at 15:59

Kermit

33,827
13
85
121

How do I get it to work with the column name instead of the @var? – Gwendolyn Wood Oct 04 '12 at 17:22
@GwendolynWood You replace each instance of `@var` with the column name. See my update – Kermit Oct 04 '12 at 17:24
My Bad, my query results were passing zeros so when I tried to change the @var with the column name I was geting Invalid Length parameter passed to the LEFT or SUBSTRING function. Thank you for the help. – Gwendolyn Wood Oct 04 '12 at 17:44

Extracting data from the middle of a string in SQL Query

3 Answers3