If my column has record like this:

"0,11.2,2,5,1-2,1,SAMPLE CLOT,',11.2 g/dl,14300,145,negative"

and column data type is text.

How can I find a record <7 (less than seven) from that column.

If i use cast(substring(p.result,1,2) as float) <7 then this error is occurred:

invalid input syntax for type double precision: " "

and for int this ERROR:

invalid input syntax for integer: "9."

So, I am stuck in there, how to do it.


select * from dg_result_entry_header h
inner join dg_result_entry_detail p 
     on h.result_entry_id = p.result_entry_id
where p.sub_investigation_id = 243 
      and h.result_date between '2018-04-01' and '2018-04-30' 
      and cast(substring(p.result,1,2) as int) <7
      and p.result is not null 

current table structure

Current Table Structure

i want only <7 count from result column and output like this

count <7


closed as unclear what you're asking by ypercubeᵀᴹ, RDFozz, mustaccio, Colin 't Hart, Lennart May 6 '18 at 16:03

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    Please Edit your question and add the CREATE TABLE statements for the tables in question adding the desired output. Those answering need a Minimal, Complete, and Verifiable example. Use formatted text please, no screen shots. – Evan Carroll May 4 '18 at 7:06
  • @McNets: Actually lab result stored in that column and i have to find a HB record >=0 and < 7 but column has int,float,char everything data is present. – S Deshmukh May 4 '18 at 7:23
  • 1
    It is a comma-separated value list. Are you looking for the first element of that list? – McNets May 4 '18 at 7:24
  • @McNets : Just i am trying that, if possible. I had try lots of thing but cant't find any proper solution for that. 0,11.2,2,5,1-2,1,SAMPLE CLOT,',11.2 g/dl,14300,145,negative each record present in separate row and from that i wanna just < 7 records those are int. – S Deshmukh May 4 '18 at 7:28
create table tbl (val text);
insert into tbl values 
('0,11.2,2,5,1-2,1,SAMPLE CLOT,'',11.2 g/dl,14300,145,negative'),
('9,11.2,2,5,1-2,1,SAMPLE CLOT,'',11.2 g/dl,14300,145,negative'),
('3,11.2,2,5,1-2,1,SAMPLE CLOT,'',11.2 g/dl,14300,145,negative'),
('8,11.2,2,5,1-2,1,SAMPLE CLOT,'',11.2 g/dl,14300,145,negative');

You can get first element of the list using string_to_array() function.

select (string_to_array(val, ','))[1] from   tbl
| string_to_array |
| :-------------- |
| 0               |
| 9               |
| 3               |
| 8               |

Or filter rows using a cast in this way:

(string_to_array(val, ','))[1]::int < 7
select *
from   tbl
where  (string_to_array(val, ','))[1]::int < 7
| val                                                         |
| :---------------------------------------------------------- |
| 0,11.2,2,5,1-2,1,SAMPLE CLOT,',11.2 g/dl,14300,145,negative |
| 3,11.2,2,5,1-2,1,SAMPLE CLOT,',11.2 g/dl,14300,145,negative |

db<>fiddle here

Not the answer you're looking for? Browse other questions tagged or ask your own question.