CODEWITHSUNDEEP
mysqlsqloracle-databasereplace
user2475677
user2475677

Reputation: 149

Using replace with select statement for a specific column in SQL oracle

Lets say I have a following table named INFO:

NAME   CITY                PROFESSION
A      New-Mexico          Software-Developer
B      Tampa               Software-Analyst

I only wish to replace the "-" in the PROFESSION column with a '/'. What should be the query to display ALL contents of the table INFO , with the above mentioned change?

I tried :

SELECT REPLACE(PROFESSION,'-','/') , * from INFO;

But this does not work.

Upvotes: 1

Views: 14056

Answers (1)

Przemyslaw Kruglej
Przemyslaw Kruglej

Reputation: 8113

When you have any other columns in your SELECT list, you can't use plain asterisk sign (*). You have to use table name or alias with it:

SELECT REPLACE(PROFESSION,'-','/') , info.* from INFO;

What you want, I guess, is:

SELECT name, city, REPLACE(PROFESSION,'-','/') AS profession FROM info;

Test:

CREATE TABLE info (
  name VARCHAR2(20),
  city VARCHAR2(20),
  profession VARCHAR2(20)
);

INSERT INTO info VALUES ('A', 'New-Mexico', 'Software-Developer');
INSERT INTO info VALUES ('B', 'Tampa', 'Software-Analyst');

COMMIT;

SELECT name, city, REPLACE(PROFESSION,'-','/') AS profession FROM info;

Output:

NAME                 CITY                 PROFESSION         
-------------------- -------------------- --------------------
A                    New-Mexico           Software/Developer   
B                    Tampa                Software/Analyst

Oracle SQLFiddle

MySQL SQLFiddle

Upvotes: 6

Related Questions