MySQL: Converting VARCHAR to DECIMAL and find between range -


i have data in column

h1.1 h1.2 h1.3 h1.10 h50.1 h50.5 h55.5 

i want search between #1.10 #50.1. have tried query not getting desired result.

select p_code table  convert(substring_index(replace(`code`,'h',''), '.', 1),signed integer) between convert(substring_index('1.1', '.', 1),signed integer)  , convert(substring_index('1.99', '.', 1),signed integer)  , convert(substring_index(replace(`code`,'h',''), '.', 1),signed integer)  between convert(substring_index('1.1', '.', -1),signed integer)  , convert(substring_index('1.99', '.', -1),signed integer)  , convert(substring_index(replace(`code`,'h',''), '.', 1),signed integer)>=convert(substring_index('1.1', '.', 1),signed integer)  , convert(substring_index(replace(`code`,'h',''), '.', 1),signed integer)<=convert(substring_index('1.99', '.', 1),signed integer) , convert(substring_index(replace(`code`,'h',''), '.', -1),signed integer)>=convert(substring_index('1.1', '.', -1),signed integer)  , convert(substring_index(replace(`code`,'h',''), '.', -1),signed integer)<=convert(substring_index('1.99', '.', -1),signed integer) order length(`code`)  desc ,`code` desc 

i have tried this:

(convert(replace(`code`,'h',''),decimal(5,3)) between convert('1.1',decimal(5,3)) , convert('1.99',decimal(5,3))) 

you temporary table :

create temporary table temp_table (     code decimal(5,3) );  insert temp_table (code) select convert(replace(`code`,'h',''), decimal(5,3)) `table`; select code temp_table code between 1.10 , 50.1; 

Comments

Popular posts from this blog

Payment information shows nothing in one page checkout page magento -

tcpdump - How to check if server received packet (acknowledged) -