Combining two columns of a table and returning it as a single column separated by a character

Question

I'm using oracle sql developer for the first time. I want to create a view with two columns of a table combined and seperated by a slash. I have done this in ssms but when I write the same code for sql dev it returns me problems like 'the specified number is invalid.

SELECT ID AS W_ID, CAST(data1 AS VARCHAR)+' - '+data2 AS W_CODE, +data3 AS W_TEXT
FROM table1
WHERE data3=1;

how can I translate this query in Oracle ?

[What is the string concatenation operator in Oracle?](https://stackoverflow.com/q/278189/266304) — Alex Poole, May 29 '19 at 14:24

Gordon Linoff · Accepted Answer · 2019-05-29T14:15:19.737

1

Oracle uses the standard || operator for string concatenation, not +:

SELECT ID AS W_ID,
       (data1 ||  ' - ' || data2) AS W_CODE,
      data3 AS W_TEXT
FROM table1
WHERE data3 = 1;

Casting to a string is unnecessary. Oracle will do that automatically. If you do, TO_CHAR() is the more colloquial method in Oracle, because it lets you control the format for numbers and dates.

I don't understand the purpose of +, so I removed it. Perhaps you want abs()?

edited May 29 '19 at 14:15

answered May 29 '19 at 14:07

Gordon Linoff

1,242,037
58
646
786

it returns me the error missing right parenthesis.. --- edit: the right parenthesis goes before the AS! Thank you very much! – helloandroiddevelopers May 29 '19 at 14:14
@helloandroiddevelopers - you don't need the parentheses at all. – Alex Poole May 29 '19 at 14:25

Combining two columns of a table and returning it as a single column separated by a character

1 Answers1