Hi,

I have a varchar(255) column where I may have data like this:

43294430949
adkk3400
1056
ff1d
10
302
15000043

I would like to write a SQL query that returns values between
10 and 500 (numeric)

If I just do this:
Select * from table where column between '10' and '500'
I would also get 15000043. That's incorrect

I also tried doing this:
Select * from table where CONVERT(int, column) >=10 and CONVERT(int,
column) <=500

but it fails when I have characters in the column.

Do you guys know how I can do that?

Thanks

Try,

Select *
from table
where
    case
    when c1 like '[0-9][0-9]' or c1 like '[0-9][0-9][0-9]' then cast(c1 as int)
    else null
    end between 10 and 500


AMB

Show quoteHide quote

First you need a solid function that can determine if the value is numeric. For
that go here: http://www.aspfaq.com/show.asp?id=2390.

Select *
From #Test As T
Where IsNumeric(T.Data) = 1
    And dbo.IsReallyInteger(T.Data) = 1
    And Cast(T.Data As BigInt) Between 10 And 50

Why the two checks? If you only use IsReallyNumeric, SQL cannot determine what
that function actually does and more specifically, whether it filters for values
that will be castable to BigInt. Then why use IsReallyInteger in the first
place? The reason is that IsNumeric is faulty in its determination of numeric
values. Characters like "$" and "d' and other odd characters can return true for
a IsNumeric.


HTH


Thomas



Show quoteHide quote

You can write your query with a WHERE clause like:

WHERE CASE WHEN ISNUMERIC( col )  = 1
            THEN CAST( col AS BIGINT )
        END BETWEEN 10 AND 500 ;

Note that there are certain considerations with ISNUMERIC with characters
like e, d, $ etc. in which case you'd have to use PATINDEX to make sure the
values are numerically compatible. Also, is the converted value is beyond
the value limitations of INT or BIGINT or even DECIMAL values, then you'll
get an overflow error.

We need more information:

1) How should the values such as 'adkk3400' be considered? Do you want this
to be 3400 numeric, or do you want to ignore rows with nun-numeric content?

2) For the numeric values, the 'int' data type would not work for your first
value '43294430949' - it is outside the rane of acceptable values. Would
'bigint' be OK?

So, for example, if you are ignoring rows with alphabetical characters, and
your data only contained numbers and letters, you could try something like
this (untested pseudo-code, since you did not provide DDL, sample data, or
expected results [http://www.aspfaq.com/etiquette.asp?id=5006]):

SELECT <Final Column List>
(SELECT UglyDataColumn, <Other Column List>
FROM MakeBelieveTable
WHERE UglyDataColumn NOT LIKE '%[A-Za-z]%') NUMONLY
WHERE CAST(NUMONLY.UglyDataColumn AS bigint) BETWEEN 10 AND 500

If this is not what you are looking for, you will need to provide better
specifications.

P.S. Out of curiosity, what type of information exactly is this
'UglyDataColumn' holding? That is a seriously bad assortment of values, and
I am guessing there are either some missing constraints on that column, or
the design is fundamentally flawed.

Show quoteHide quote

SQL HELP
INSTEAD OF INSERT TRIGGER AND NULL
obtaining the ip address of connection
Use a stored procedure from a function
triggers