How to get the result of table contains numeric and strings using where condition

Question

I have a table like below

If I select item_no>'1623' from above table

I want to print the result below

1666
1674
1912
1952
1953

I am trying below command

select * from table where item_no>'1623'

But it's giving wrong result

Answer 1

Just try to cast item_no as integer ( I suppose it's non-numeric ).

with tab(numbers) as
(
 select nullif(left(item_no, strpos(item_no, '_') - 1),'')::integer from "table"    
)    
select numbers
  from tab
 where numbers > 1623     

( without seeing the picture, considering the comment all of the data end with a non-numeric character ) all data composed of digits upto the last character.

Or, try to extract the digits only as :

with tab(numbers) as
(
 select nullif(regexp_replace(col, '\D','','g'),'')::integer from "table"    
)    
select numbers
  from tab
 where numbers > 1623

Demo

Answer 2

use SUBSTRING

select * from t where substring(item_no,'([0-9]+)') :: int  > 1623

DEMO