SQL: Filter on Alpha and numeric on column of Type TEXT

Question

I've got a column of type Text. In the column are numeric values such as4, 8, 3.2, etc... and also values such as 'Negative', 'Positive', '27A', '2pos 1neg'.

The user needs to be able to say: "Give me all the values between 10 and 30, and also the values that are 'Negative'. The WHERE clause would need to do something along the lines of:

WHERE Tbl.Col > 10
AND   Tbl.Col < 30
AND   Tbl.Col = 'Negative'

This is problematic for obvious reasons. I've tried using the ISNUMERIC function to alleviate the issue but can't seem to get exactly what i need. I can either get all the alpha values in the column, or all the numeric values in the column as floats but cant seem to filter on both at the same time. To grab all the Numeric values I've been using this:

SELECT Num.Val FROM 
    (SELECT Val = CASE ISNUMERIC(CAST(TBL.COL AS VARCHAR)) WHEN 1 
             THEN CAST(CAST(TBL.COL AS VARCHAR) AS FLOAT) ELSE NULL END
            FROM Table TBL
            WHERE TBL.COL IS NOT NULL ) as Num
    WHERE Num.val IS NOT NULL
    AND   Num.val > 10

Answer 1

First I would go slap the person who designed the table (hopefully it isn't you) :>

Go to here and get the split table function. I would then convert the text column (like you have in example above) into varchar(max) and supply it as the parameter to the split function. Then you could select from the table results of the split function using the user supplied parameters.

Answer 2

If I understand the issue correctly something like this should get you close.

with MyNumbers as
(
    select t.Col
    from Tbl t
    --where ISNUMERIC(t.Col) = 1
    where t.Col NOT LIKE '%[^0-9.]%'
)
, MyAlpha as
(
    select t.Col
    from Tbl t
    where ISNUMERIC(t.Col) = 0
)

select Col
from MyNumbers
where Col > 10
    and Col < 30

union all

select Col
from MyAlpha
where ColorMatch = ' Negative'

Answer 3

I have found the answer to my problem:

SELECT
al_Value   =    Table.Column
FROM Table
WHERE ( 
        ISNUMERIC(CAST(Table.Column AS VARCHAR)) = 1 AND 
        CONVERT(FLOAT, CAST(Table.Column AS VARCHAR)) > 1.0 AND 
        CONVERT(FLOAT, CAST(Table.Column AS VARCHAR)) < 10.0
      )    
OR    ( 
        CAST(Table.Column AS VARCHAR) IN ('negative', 'no bueno') 
      )

This will return one column named 'al_Value' that filters on Table.Column (which is of Datatype TEXT) and apply the filters in the WHERE clause above.

Thanks everyone for trying to help me with this issue.