Error In Sql Query


#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.


#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

https://www.sitepoint.com/community/t/between-operator-for-varchar-data-type/236042/2


#11

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