CODEWITHSUNDEEP
sqloracle-database
shivam
shivam

Reputation: 383

Add double quotes to SQL output for columns with spaces

Suppose I have three column Person_Number, Name and Address. Now there is a requirement to add double quotes if the particular field has spaces.

How this can be done ?

Upvotes: 0

Views: 695

Answers (1)

Popeye
Popeye

Reputation: 35900

You can use INSTR and CASE statement as follows:

SELECT
    CASE
        WHEN INSTR(YOUR_COLUMN, ' ') > 0 
        THEN '"'
              || YOUR_COLUMN
              || '"'
        ELSE YOUR_COLUMN
    END
FROM YOUR_TABLE;

If you want to update the table then you can use the following query:

UPDATE YOUR_TABLE
SET Person_Number = CASE WHEN INSTR(Person_Number,' ')>0
                         THEN '"' || Person_Number || '"' 
                         ELSE Person_Number 
                    END,
    Name = CASE WHEN INSTR(Name,' ')>0 
                THEN '"' || Name || '"' 
                ELSE Name 
           END,
    Address = CASE WHEN INSTR(Address,' ')>0 
                   THEN '"' || Address || '"' 
                   ELSE Address 
              END
WHERE INSTR(Person_Number,' ')>0 
   OR INSTR(Name,' ')>0 
   OR INSTR(Address,' ')>0;

Upvotes: 3

Related Questions