Your DB is obviously result of some survey, and it seems to contain the original survey data. The usual way is to run this through an ECCD (Extract, Clean, Conform, Deliver) process and store clean and standardized data into a separate database (maybe a warehouse) which can then be used for analytics and reporting.
If you have SSIS use data profiling task to get an idea of types of strings you have in there. The Column Pattern Profile reports a set of regular expressions on the string column, so you will get an idea of what's inside those strings. If you do not have SSIS, you can use eobjects DataCleaner to do the same.
If you can not spare a new database or at least a new table -- at minimum add a numeric column to this table and then extract numeric values form those strings into the new column. You may want to use "something else" (SSIS, Pentaho Kettle, Python, VB, C#) to do this -- in general T-SQL in not very good at string processing.
My guess is that this is not the only column that has garbage inside, so any analysis that you may run on this may be worthless.
And if you still think that the ranges are the only problem, this example may help:
First some data
DECLARE @myTable TABLE (
AlUnits varchar(10)
) ;
INSERT INTO @myTable
(AlUnits )
VALUES ( '10' )
, ( '15' )
, ( '20' )
, ( '7 - 12' )
, ( '3 - 5' )
;
The query splits records into two groups, numeric and not numeric -- assumed ranges.
;
WITH is_num
AS ( SELECT CAST(AlUnits AS decimal(6, 2)) AS Units_LO
,CAST(AlUnits AS decimal(6, 2)) AS Units_HI
FROM @myTable
WHERE ISNUMERIC(AlUnits) = 1
),
is_not_num
AS ( SELECT CAST( RTRIM(LTRIM(LEFT(AlUnits,
CHARINDEX('-', AlUnits) - 1)))
AS decimal(6,2)) AS Units_LO
,CAST(RTRIM(LTRIM(RIGHT(AlUnits,
LEN(AlUnits)
- CHARINDEX('-', AlUnits))))
AS decimal(6,2)) AS Units_HI
FROM @myTable
WHERE ISNUMERIC(AlUnits) = 0
)
SELECT Units_LO
,Units_HI
,CAST(( Units_LO + Units_HI ) / 2.0 AS decimal(6, 2)) AS Units_Avg
FROM is_num
UNION ALL
SELECT Units_LO
,Units_HI
,CAST(( Units_LO + Units_HI ) / 2.0 AS decimal(6, 2)) AS Units_Avg
FROM is_not_num ;
Returns:
Units_LO Units_HI Units_Avg
----------- ----------- ----------
10.00 10.00 10.00
15.00 15.00 15.00
20.00 20.00 20.00
7.00 12.00 9.50
3.00 5.00 4.00