SQL Server 2005: Get first word in a varchar that matches

I have a varchar column in a SQL Server 2005 table that looks like the following:

Mainly Sunny, 13.7°C
Partly cloudy, 12°C, Humidity 69%, Wind NE 15km/h

My goal is to parse out the temperature values, 13.7 and 12 respectively. Is there a series of string functions that can be used to locate and retrieve the first word in each string that contains °C?

Solution

DECLARE @t TABLE(s VARCHAR(255));

INSERT @t SELECT 'Mainly Sunny, 13.7°C'
UNION ALL SELECT 'Partly cloudy, 12°C, Humidity 69%, Wind NE 15km/h';

SELECT RIGHT(LEFT(s, CHARINDEX('°', s)-1), 
  CHARINDEX(' ', REVERSE(LEFT(s, CHARINDEX('°', s)-1)))-1) FROM @t;

So, as a computed column:

DECLARE @t TABLE
(
  s VARCHAR(255), 
  x AS CONVERT(VARCHAR(255),RIGHT(LEFT(s, CHARINDEX('°', s)-1), 
    CHARINDEX(' ', REVERSE(LEFT(s, CHARINDEX('°', s)-1)))-1)) PERSISTED
);

INSERT @t SELECT 'Mainly Sunny, 13.7°C'
UNION ALL SELECT 'Partly cloudy, 12°C, Humidity 69%, Wind NE 15km/h';

SELECT s,x FROM @t;

Results:

Mainly Sunny, 13.7°C                                  13.7
Partly cloudy, 12°C, Humidity 69%, Wind NE 15km/h     12

If you might have strings that don't contain a ° symbol, then:

DECLARE @t TABLE
(
  s VARCHAR(255), 
  x AS CONVERT(VARCHAR(255), CASE WHEN CHARINDEX('°', s) > 0 THEN 
    RIGHT(LEFT(s, CHARINDEX('°', s)-1), 
    CHARINDEX(' ', REVERSE(LEFT(s, CHARINDEX('°', s)-1)))-1) END) PERSISTED
);

INSERT @t SELECT 'Mainly Sunny, 13.7°C'
UNION ALL SELECT 'Partly cloudy, 12°C, Humidity 69%, Wind NE 15km/h'
UNION ALL SELECT 'No weather to report';

SELECT s,x FROM @t;

Results:

Mainly Sunny, 13.7°C                                  13.7
Partly cloudy, 12°C, Humidity 69%, Wind NE 15km/h     12
No weather to report                                  NULL

(If you want something else instead of NULL, I can't imagine what, you can add an ELSE to the CASE expression.)

Also, to prove my solution is flexible without introducing a performance-crippling user-defined function:

DECLARE @SearchString VARCHAR(8000);
SET @SearchString = 'km/h'; -- change this to '°'

DECLARE @t TABLE
(
  s VARCHAR(255)
);

INSERT @t SELECT 'Mainly Sunny, 13.7°C'
UNION ALL SELECT 'Partly cloudy, 12°C, Humidity 69%, Wind NE 15km/h'
UNION ALL SELECT 'No weather to report'
UNION ALL SELECT 'Wind 102km/h, 23.5°C, mostly cloudy';

SELECT s, x = CONVERT(VARCHAR(255), CASE WHEN CHARINDEX(@SearchString, s) > 0 THEN 
    RIGHT(LEFT(s, CHARINDEX(@SearchString, s)-1), 
    CHARINDEX(' ', REVERSE(LEFT(s, CHARINDEX(@SearchString, s)-1)))-1) END)
FROM @t;

Results:

Mainly Sunny, 13.7°C                                NULL
Partly cloudy, 12°C, Humidity 69%, Wind NE 15km/h   15
No weather to report                                NULL
Wind 102km/h, 23.5°C, mostly cloudy                 102