I am looking to convert a select statement from oracle to postgres

Question

select max(to_number(ltrim(aefo_number,'VE')))
from   exemption
where  aefo_number like 'E%'
or     aefo_number like 'V%'

I am getting Function to_number(text) does not exist error for the above select statement and I am unable to convert it.

If anyone know the syntax for the select statement please let me know

What error? Does the error say that a specific function doesn't exist ? Shouldn't you try to find the equivalent? — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Nov 23, 2022 at 14:24
not sure ltrim works that way in postgresql you may have to use case expression to evaluate and replace or regular expressions. — xQbert
– xQbert, Commented Nov 23, 2022 at 14:25
What version of postgres? Maybe you need to just cast to number... stackoverflow.com/questions/37294045/… postgresql.org/docs/current/functions-formatting.html shows the formatting is needed on to_number(text,text) — xQbert
– xQbert, Commented Nov 23, 2022 at 14:28
Did you check the manual and functions? postgresql.org/docs/current/functions.html — Frank Heikens
– Frank Heikens, Commented Nov 23, 2022 at 14:31

user330315 · Accepted Answer · 2022-11-23 14:36:41Z

2

Unlike Oracle, Postgres' to_number() function always needs a format mask. So you would need something like to_number(ltrim(aefo_number,'VE'), '99999999999')

If you don't want to (or can't) specify a format mask, you could cast the value to a numeric oder integer:

select max(ltrim(aefo_number,'VE')::integer)
from   exemption
where  aefo_number like 'E%'
or     aefo_number like 'V%'

answered Nov 23, 2022 at 14:36

user330315

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

2 Comments

Harish Over a year ago

Syntax error is showing mr.horse for 2nd one and 1st method one also not working

user330315 Over a year ago

@Harish: then you are running a different statement. The code in my answer certainly works

MT0 · Accepted Answer · 2022-11-23 14:43:25Z

0

If V and E are only the first character then you can use:

select MAX(SUBSTR(aefo_number,2)::integer)
from   exemption
where  aefo_number like 'E%'
or     aefo_number like 'V%'

fiddle

answered Nov 23, 2022 at 14:43

MT0

173k12 gold badges70 silver badges136 bronze badges

2 Comments

Harish Over a year ago

invalid input syntax for type integer is showing @ Mto for yours

MT0 Over a year ago

@Harish Please look at the fiddle linked in my answer; it shows the code working in PostgreSQL without any syntax errors. Are you sure you are running the same code and that you are using PostgreSQL?

Collectives™ on Stack Overflow

I am looking to convert a select statement from oracle to postgres

2 Answers 2

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related