Oracle query error

Question

I'm quite new to Oracle and seem to having some problems with my query

Here's what i've got

SELECT (REPLACE(column1, 'test', '') + ': ' + column2) AS column3 FROM table

I get a ORA-01722 invalid number error with this.

I think it's something to do with the +'s but i'm not sure what the correct syntax is.

Any ideas?

Bill the Lizard · Accepted Answer · 2011-11-06 12:22:30Z

8

You should use || to concat two strings...

SELECT (REPLACE(column1, 'test', '') || ': ' || column2) AS column3 FROM table

Bill the Lizard

407k213 gold badges579 silver badges892 bronze badges

answered Jul 14, 2011 at 9:15

4,77928 silver badges32 bronze badges

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

Comments

Lukas Eder · Accepted Answer · 2011-07-14 09:14:10Z

4

The correct way to concatenate in Oracle is by using ||

select 'a' || 'b' from dual;

answered Jul 14, 2011 at 9:14

Lukas Eder

224k138 gold badges731 silver badges1.6k bronze badges

Rob van Wijk · Accepted Answer · 2011-07-14 09:17:23Z

4

or by using the CONCAT function

(which nobody uses because the double pipe is easier to use, so just trying to be complete here)

Regards,
Rob.

answered Jul 14, 2011 at 9:17

Rob van Wijk

17.7k5 gold badges41 silver badges56 bronze badges