Error In Sql Query

Databases
1

This Query

select *
from product
where cast(replace(phdd, 'GB',' ') as int) between 1 and 100

gives me an error:

right syntax to use near 'int) between 1 and 100
2

Mysql?

The nincompoops at Mysql requires that you specify signed or unsigned when casting to integer.

select *
from product
where cast(replace(phdd, 'GB',' ') as signed integer) between 1 and 100
3

Ok! fine
using this No error in SQL query.

But still It returns the rows that have 1TB :persevere:

4

Which exact records do you wish to retrieve?

Give some sample data and the expected result.

If you only wish to get records where GB occurs add

and phdd like '%GB%'
5

Ok here it is:

and I want to select the records between 1GB to 500GB
and 1TB to 100TB

6 
select *
  from product
 where (cast(replace(phdd, 'GB',' ') as signed integer) between 1 and 500
   and phdd like '%GB%')
    or (cast(replace(phdd, 'TB',' ') as signed integer) between 1 and 100
   and phdd like '%TB%')
7

It would have been much easier if you stored the values as an integer all relating to one unit or measurement.

2 Likes
8

That’s what I said

9

Where is that post? :confused:

10

Hmmm, odd. I would have bet oneboxed posts linked to the source post.

anyway, it was in one of neha_k’s other topics

BETWEEN OPERATOR for VARCHAR data type - #2 by Mittineague

1 Like
11

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.