Converting varchar to int

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?
ThanksFirst 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 wh
at
that function actually does and more specifically, whether it filters for va
lues
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 numeri
c
values. Characters like "$" and "d' and other odd characters can return true
for
a IsNumeric.
HTH
Thomas
"Star" <noemail@.noemail.com> wrote in message
news:%23RPTMgtjFHA.3544@.TK2MSFTNGP15.phx.gbl...
> 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, colum
n)
> <=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
"Star" wrote:

> 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
>|||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.
Anith|||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.
"Star" <noemail@.noemail.com> wrote in message
news:%23RPTMgtjFHA.3544@.TK2MSFTNGP15.phx.gbl...
> 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