Skip to content
Advertisement

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

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 ?

Advertisement

Answer

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()?

User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement