SQL Substring

I cant seem to find an efficient way to parse the negative values of these strings
Sample data
'	 -220 +180 -5 -110 +5 -110  Wetver-Dryerland-test-october-21-2013'
'	 -200 +170 -3.5 -110 +3.5 -110  Wetver-Dryerland-test-october-21-2013'
'	 -185 +165 -3.5 -110 +3.5 -110  Wetver-Dryerland-test-october-21-2013'
'	 -190 +165 -3.5 -110 +3.5 -110  Wetver-Dryerland-test-october-21-2013'
'	 -190 +160 -3.5 -110 +3.5 -110  Wetver-Dryerland-test-october-21-2013'
'	 -185 +170 -3.5 -109 +3.5 -101  Wetver-Dryerland-test-october-21-2013'
'	 -210 +175 -4.5 -110 +4.5 -110  Wetver-Dryerland-test-october-21-2013'

Open in new window



My query should return this
-5
-3.5
-3.5
-3.5
-3.5
-3.5
-4.5
LVL 8
Leo TorresSQL DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Najam UddinCommented:
-110 is not part of result?
chaauCommented:
It is possible to do with some CROSS APPLY magic:
-- drop table #t
create table #t(t varchar(100))
insert into #t values('	 -220 +180 -5 -110 +5 -110  Wetver-Dryerland-test-october-21-2013')
insert into #t values('	 -200 +170 -3.5 -110 +3.5 -110  Wetver-Dryerland-test-october-21-2013')
insert into #t values('	 -185 +165 -3.5 -110 +3.5 -110  Wetver-Dryerland-test-october-21-2013')
insert into #t values('	 -190 +165 -3.5 -110 +3.5 -110  Wetver-Dryerland-test-october-21-2013')
insert into #t values('	 -190 +160 -3.5 -110 +3.5 -110  Wetver-Dryerland-test-october-21-2013')
insert into #t values('	 -185 +170 -3.5 -109 +3.5 -101  Wetver-Dryerland-test-october-21-2013')
insert into #t values('	 -210 +175 -4.5 -110 +4.5 -110  Wetver-Dryerland-test-october-21-2013')

select t, s.s from #t
CROSS APPLY(SELECT REPLACE(t, CHAR(9), '') as s) s1
CROSS APPLY(SELECT LTRIM(s1.s) as s) s2
CROSS APPLY(SELECT RTRIM(LTRIM(
   CASE WHEN CHARINDEX(' ', s2.s) > 0 THEN 
     CASE WHEN CHARINDEX(' ', s2.s, CHARINDEX(' ', s2.s)+1) > 0 THEN 
       CASE WHEN CHARINDEX(' ', s2.s, CHARINDEX(' ', s2.s, CHARINDEX(' ', s2.s)+1)+1) > 0 THEN
          SUBSTRING(s2.s, CHARINDEX(' ', s2.s, CHARINDEX(' ', s2.s)+1), CHARINDEX(' ', s2.s, CHARINDEX(' ', s2.s, CHARINDEX(' ', s2.s)+1)+1) - CHARINDEX(' ', s2.s, CHARINDEX(' ', s2.s)+1)) 
       END
     END
   END)) as s
) s

Open in new window

Vikas GargAssociate Principal EngineerCommented:
Hi,

You can do this by this single liner

-- drop table #t
create table #t(t varchar(100))
insert into #t values('	 -220 +180 -5 -110 +5 -110  Wetver-Dryerland-test-october-21-2013')
insert into #t values('	 -200 +170 -3.5 -110 +3.5 -110  Wetver-Dryerland-test-october-21-2013')
insert into #t values('	 -185 +165 -3.5 -110 +3.5 -110  Wetver-Dryerland-test-october-21-2013')
insert into #t values('	 -190 +165 -3.5 -110 +3.5 -110  Wetver-Dryerland-test-october-21-2013')
insert into #t values('	 -190 +160 -3.5 -110 +3.5 -110  Wetver-Dryerland-test-october-21-2013')
insert into #t values('	 -185 +170 -3.5 -109 +3.5 -101  Wetver-Dryerland-test-october-21-2013')
insert into #t values('	 -210 +175 -4.5 -110 +4.5 -110  Wetver-Dryerland-test-october-21-2013')

SELECT SUBSTRING(SUBSTRING(T,CHARINDEX(' -',SUBSTRING(T,CHARINDEX(' -', T)+2,100))+3,5),1,
CASE WHEN CHARINDEX(' -',SUBSTRING(T,CHARINDEX(' -',SUBSTRING(T,CHARINDEX(' -', T)+2,100))+4,5))=0 THEN 5 ELSE
CHARINDEX(' -',SUBSTRING(T,CHARINDEX(' -',SUBSTRING(T,CHARINDEX(' -', T)+2,100))+4,5)) END)
FROM #T 

Open in new window

10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

PortletPaulEE Topic AdvisorCommented:
An alternative approach is to treat those strings as being "space delimited"

i.e.

'       -220 +180 -5 -110 +5 -110  Wetver-Dryerland-test-october-21-2013'
broken into pieces is:

-220
+180
-5
-110
+5
-110  
Wetver-Dryerland-test-october-21-2013

So a general purpose "split string" function could be used, as long as it works with ' ' as a parameter.

With that in mind, this function (from here, by Scott Pletcher)
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[SplitStringIntoTable]  (
    @string varchar(8000),
    @delimiter varchar(5)
    )
RETURNS TABLE WITH SCHEMABINDING 
AS
--SELECT * FROM (SELECT 'ab/c/def/ghijklm/no/prq/////st/u//' AS string) AS test_values CROSS APPLY DBA.dbo.SplitStringIntoTable(test_values.string, '/')
RETURN
--Inline-CTE-Driven "tally table" produces values from 0 to 9999  ... enough to cover varchar(8000)
WITH cteDigits AS (
    SELECT 0 AS digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
    SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
),
cteTally AS (
    SELECT [1000s].digit * 1000 + [100s].digit * 100 + [10s].digit * 10 + [1s].digit AS num
    FROM cteDigits [1s] --0 thru 9
    CROSS JOIN cteDigits [10s] --thru 99
    CROSS JOIN cteDigits [100s] --thru 999
    CROSS JOIN cteDigits [1000s] --thru 9999
),
cteBase(num) AS ( --Limit the number of rows up front, for both a performance gain and prevention of accidental "overruns"
        SELECT 0 UNION ALL
        SELECT TOP (DATALENGTH(ISNULL(@string, 1))) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM cteTally
), 
cteStart(num1) AS ( --This returns num + 1 (starting position of each "value" just once for each delimiter)
        SELECT b.num + 1
        FROM cteBase b
        WHERE (SUBSTRING(@string, b.num, 1) = @delimiter OR b.num = 0)
)
--Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found. 
SELECT ROW_NUMBER() OVER(ORDER BY s.num1) AS value_seq,
       LTRIM(RTRIM(SUBSTRING(@string, s.num1, ISNULL(NULLIF(CHARINDEX(@delimiter, @string, s.num1), 0) - s.num1, 8000)))) AS value
FROM cteStart s;

GO

Open in new window

Can be used like this:
--drop table #t
create table #t(t varchar(100))
insert into #t values('	 -220 +180 -5 -110 +5 -110  Wetver-Dryerland-test-october-21-2013')
insert into #t values('	 -200 +170 -3.5 -110 +3.5 -110  Wetver-Dryerland-test-october-21-2013')
insert into #t values('	 -185 +165 -3.5 -110 +3.5 -110  Wetver-Dryerland-test-october-21-2013')
insert into #t values('	 -190 +165 -3.5 -110 +3.5 -110  Wetver-Dryerland-test-october-21-2013')
insert into #t values('	 -190 +160 -3.5 -110 +3.5 -110  Wetver-Dryerland-test-october-21-2013')
insert into #t values('	 -185 +170 -3.5 -109 +3.5 -101  Wetver-Dryerland-test-october-21-2013')
insert into #t values('	 -210 +175 -4.5 -110 +4.5 -110  Wetver-Dryerland-test-october-21-2013')

SELECT 
      SplitString.value, #t.t
FROM #t
CROSS APPLY dbo.SplitStringIntoTable ( #t.t, SPACE(1) ) as SplitString
WHERE SplitString.value_seq = 4

Open in new window

Which produces this result:
| value |                                                                      t |
|-------|------------------------------------------------------------------------|
|    -5 |     	 -220 +180 -5 -110 +5 -110  Wetver-Dryerland-test-october-21-2013 |
|  -3.5 | 	 -200 +170 -3.5 -110 +3.5 -110  Wetver-Dryerland-test-october-21-2013 |
|  -3.5 | 	 -185 +165 -3.5 -110 +3.5 -110  Wetver-Dryerland-test-october-21-2013 |
|  -3.5 | 	 -190 +165 -3.5 -110 +3.5 -110  Wetver-Dryerland-test-october-21-2013 |
|  -3.5 | 	 -190 +160 -3.5 -110 +3.5 -110  Wetver-Dryerland-test-october-21-2013 |
|  -3.5 | 	 -185 +170 -3.5 -109 +3.5 -101  Wetver-Dryerland-test-october-21-2013 |
|  -4.5 | 	 -210 +175 -4.5 -110 +4.5 -110  Wetver-Dryerland-test-october-21-2013 |

Open in new window

also see: http://sqlfiddle.com/#!6/f5e4e/3

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Leo TorresSQL DeveloperAuthor Commented:
Thank you guys for all the comments. I will have to test these solutions. I should provided a little better sample the signs can change. I added to more rows at the bottom.
'	 -220 +180 -5 -110 +5 -110  Wetver-Dryerland-test-october-21-2013'
'	 -200 +170 -3.5 -110 +3.5 -110  Wetver-Dryerland-test-october-21-2013'
'	 -185 +165 -3.5 -110 +3.5 -110  Wetver-Dryerland-test-october-21-2013'
'	 -190 +165 -3.5 -110 +3.5 -110  Wetver-Dryerland-test-october-21-2013'
'	 -190 +160 -3.5 -110 +3.5 -110  Wetver-Dryerland-test-october-21-2013'
'	 -185 +170 -3.5 -109 +3.5 -101  Wetver-Dryerland-test-october-21-2013'
'	 -210 +175 -4.5 -110 +4.5 -110  Wetver-Dryerland-test-october-21-2013'
'	 +210 -175 -4.5 -110 +4.5 -110  Wetver-Dryerland-test-october-21-2013'
'	 +210 +175 +4.5 +110 -4.5 -110  Wetver-Dryerland-test-october-21-2013'

Open in new window

Leo TorresSQL DeveloperAuthor Commented:
This was cleaner. The other solutions did not always work. Thank you!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.