Between Operator for varchar datatype I have column 'hdd' having values '1GB','500GB,''1TB' and '100TB'

When retrieving rows where value between 1TB  and 100 TB like this:

select * from product where hdd between '1TB' and '100TB'


It returns the rows that contain 1GB also.

Also tried like this:

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


It said error near ')int 

I'm using mysqli.
The column that you have is a VARCHAR so the ordering applied by the RDBMS will be alphabetical instead of numeric.

What is the smallest possible unit of storage for a HDD these days? GB? One way to solve your problem would be to create another column that transforms the storage into GB (or some other unit). Make sure that this column is integer or numeric and not a VARCHAR. Using your example data and assuming 1000 GB per TB:

'1GB', 1
'500GB', 500
'1TB', 1000
'100TB', 100000


Filtering on the second column is a much more straightforward way to get the results that you want.

If you don't want to create a new column, it would be possible, but messy, to do that conversion within the SQL query.