Pull a range of records from a char(8) field

11 Aug 2006 8:59 PM

TCHolzer

I need to pull a range of records based on the street number. The number
field is char(8). The problem occurs when a letter was entered in the number
field as well as in 300A, 3505B, etc. Both of the examples below return an
error:

Conversion failed when converting the varchar value '3505A ' to data type
int.
or:
Error converting data type varchar to numeric.

select * from omain where 1100 < number and 3000 > number
select * from omain where cast((number) as decimal(10,0)) > 1000 and
cast((number) as decimal(10,0)) < 3000

Recommendations?

11 Aug 2006 9:23 PM

Reg Besseling

Hi

this will stop the error but you need to decide what to do about your
"dirty" data

cast((case when isnumeric(number) =1 then number else 0 end) as
decimal(10,0)) > 1000

HTH

--
Regards

Reg Besseling

Show quote

"TCHolzer" wrote:

> I need to pull a range of records based on the street number. The number
> field is char(8). The problem occurs when a letter was entered in the number
> field as well as in 300A, 3505B, etc. Both of the examples below return an
> error:
>
> Conversion failed when converting the varchar value '3505A ' to data type
> int.
> or:
> Error converting data type varchar to numeric.
>
> select * from omain where 1100 < number and 3000 > number
> select * from omain where cast((number) as decimal(10,0)) > 1000 and
> cast((number) as decimal(10,0)) < 3000
>
> Recommendations?
>
>
>

11 Aug 2006 9:32 PM

Michael Keating

Show quote

"TCHolzer" <program***@hotmail.com> wrote in message
news:uD14$jYvGHA.4140@TK2MSFTNGP03.phx.gbl...
>I need to pull a range of records based on the street number. The number
>field is char(8). The problem occurs when a letter was entered in the
>number field as well as in 300A, 3505B, etc. Both of the examples below
>return an error:
>
> Conversion failed when converting the varchar value '3505A ' to data type
> int.
> or:
> Error converting data type varchar to numeric.
>
> select * from omain where 1100 < number and 3000 > number
> select * from omain where cast((number) as decimal(10,0)) > 1000 and
> cast((number) as decimal(10,0)) < 3000
>
> Recommendations?
>
>

This will work if you know that there is only one letter, otherwise the case
statment has to get more complex;

WHERE
(CASE
WHEN Number LIKE '%[A-Z]'
THEN CONVERT(int, LEFT(Number, len(Number) - 1))
ELSE CONVERT(int, Number)
END > 1000)
AND
(CASE
WHEN Number LIKE '%[A-Z]'
THEN CONVERT(int, LEFT(Number, len(Number) - 1))
ELSE CONVERT(int, Number)
END < 3000)

MFK.

14 Aug 2006 2:51 PM

TCH

For any lurkers,

This seems to work:

SELECT CAST(LEFT(Number, PATINDEX('%[-,A-Z]%', Number + 'A')-1) AS int),
Number FROM occ_main WHERE CAST(LEFT(Number,
PATINDEX('%[-,/\@#$%^!&*()+A-Z]%', Number + 'A')-1) AS int) BETWEEN 302 and
309

(Thanks to Sergey Berezniker and Borislav Borissov for the solution)

Show quote

"TCHolzer" <program***@hotmail.com> wrote in message
news:uD14$jYvGHA.4140@TK2MSFTNGP03.phx.gbl...
>I need to pull a range of records based on the street number. The number
>field is char(8). The problem occurs when a letter was entered in the
>number field as well as in 300A, 3505B, etc. Both of the examples below
>return an error:
>
> Conversion failed when converting the varchar value '3505A ' to data type
> int.
> or:
> Error converting data type varchar to numeric.
>
> select * from omain where 1100 < number and 3000 > number
> select * from omain where cast((number) as decimal(10,0)) > 1000 and
> cast((number) as decimal(10,0)) < 3000
>
> Recommendations?
>
>

17 Aug 2006 1:14 PM

TCH

For any lurkers, found the below syntax works:

SELECT name, CAST(LEFT(Number, PATINDEX('%[-, /\@#$%^!&*()+A-Z]%', Number +
'A')-1) AS int), Number, street FROM tbl_main

WHERE CAST(LEFT(Number, PATINDEX('%[-, /\@#$%^!&*()+A-Z]%', Number + 'A')-1)
AS int) BETWEEN 23 AND 40

Show quote

"TCHolzer" <program***@hotmail.com> wrote in message
news:uD14$jYvGHA.4140@TK2MSFTNGP03.phx.gbl...
>I need to pull a range of records based on the street number. The number
>field is char(8). The problem occurs when a letter was entered in the
>number field as well as in 300A, 3505B, etc. Both of the examples below
>return an error:
>
> Conversion failed when converting the varchar value '3505A ' to data type
> int.
> or:
> Error converting data type varchar to numeric.
>
> select * from omain where 1100 < number and 3000 > number
> select * from omain where cast((number) as decimal(10,0)) > 1000 and
> cast((number) as decimal(10,0)) < 3000
>
> Recommendations?
>
>