CODEWITHSUNDEEP
sqloracle-databaset-sqlselect
user4244510
user4244510

Reputation: 79

Add keyword before and after cast() in oracle

I would like to add a keyword before and after each field value in Oracle.

For example if I am getting 123 as my ID, I would like to make it

Test123Test

Here is my query:

SELECT
CAST("ID" as varchar(10))
FROM
TABLENAME;

I have tried add + "Test" but it is giving me error.

Upvotes: 0

Views: 75

Answers (2)

Lalit Kumar B
Lalit Kumar B

Reputation: 49082

I have tried add + "Test" but it is giving me error.

Perhaps, + is used as concatenation in SQL Server. In Oracle, you could use the CONCAT function or the || operator.

The concat function is restricted to only two strings. You can have a look the concat function in the documentation http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions026.htm.

Let's see an example using the operator -

SELECT 'test' || to_char(id) || 'test' new_id
FROM   TABLENAME

Upvotes: 0

Patrick Hofman
Patrick Hofman

Reputation: 156978

Use || instead of + to concatenate strings in Oracle.

SELECT 'test' || CAST(ID as varchar(10)) || 'test'
FROM   TABLENAME

Note that I removed the " around ID too, since you most likely won't need them and they can cause problems when it unintended strictly matches column names.

Upvotes: 2

Related Questions