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

I am trying below command

select * from table where item_no>'1623'

But it's giving wrong result

Kaushik Nayak · Accepted Answer · 2019-07-11 06:50:56Z

1

use SUBSTRING

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

DEMO

answered Jul 11, 2019 at 6:50

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

joe Over a year ago

thanks its worked. and if i select item-no>1623G can i get the result 1623H and 1623I and remaining values

Barbaros Özhan · Accepted Answer · 2019-07-11 07:15:05Z

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

edited Jul 11, 2019 at 7:15

answered Jul 11, 2019 at 6:39

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

2 Comments

joe Over a year ago

getting below error ERROR: invalid input syntax for integer: "1623G" ********** Error ********** ERROR: invalid input syntax for integer: "1623G" SQL state: 22P02

Barbaros Özhan Over a year ago

@joe please share a text that picture I cannot see it. Btw, 1623G is not numeric of course. Do all data end with a G character ?

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related