Numeric comparison of a varchar field in SQL Server

I want to write a que开发者_开发百科ry to see if a category field is within a certain range. The problem is the field can contain null, text or numeric text prefixed by '#' character.

Does anybody know of SQL that will strip the non numerics and allow me to do the following check.

category > 1 and category < 100

Here is a sample of what the field category can contain:

#230.1
#200
Null
text

I am using SQL Server 2000

I appears astander's solution is functional. You should consider however a few points:

• If the table holds more than a few thousand rows, and if this type of query is to be run frequently, it may be beneficial to introduce a new column to hold the numeric value of the category (if available, null otherwise). This will be more efficient for two reasons: as written, SQL needs to scan the table, completely, i.e.it needs to review every single row; also it needs to perform all these conversion which are a bit expensive, CPU-wise.
• You may consider introducing some extra logic to normalize the category field. For example to get rid of common leading or trailing characters etc. This will "rescue" several category codes which would otherwise translate to null wouldn't be able to participate in these filters.

Try something like this

DECLARE @Table TABLE(
        Val VARCHAR(200)
)

INSERT INTO @Table (Val) SELECT '#230.1'
INSERT INTO @Table (Val) SELECT '#200'
INSERT INTO @Table (Val) SELECT '210'
INSERT INTO @Table (Val) SELECT NULL
INSERT INTO @Table (Val) SELECT 'text'

SELECT  *
FROM    (
            SELECT  CASE
                        WHEN  ISNUMERIC(Val) = 1
                            THEN CAST(Val AS FLOAT)
                        WHEN LEN(Val) > 1 AND   ISNUMERIC(RIGHT(Val,LEN(Val)-1)) = 1
                            THEN CAST(RIGHT(Val,LEN(Val)-1) AS FLOAT)
                    END Num
            FROM    @Table
            WHERE   Val IS NOT NULL
            AND     (
                            ISNUMERIC(Val) = 1
                        OR  (
                                    LEN(Val) > 1
                                AND ISNUMERIC(RIGHT(Val,LEN(Val)-1)) = 1
                            )
                    )
        ) Numbers
WHERE   Num BETWEEN 205 AND 230