Inserting multiple values with single quotes in single field

Database

Inserting multiple values with single quotes in single field

I have a table 'T' with two columns - Row_ID and Country_Code. The data in table should look like this. 

Row_ID   Country_Code

1              'USA', 'CHN', 'IND', 'GBR'

2               'MEX', 'CAN', 'FRA', 'GER'

3               'SL', 'BAN', 'JPN', 'SWZ'

How do I insert this data using a simple insert ?  I know I can work this out by 'Import' feature in SQL Assistant but I am looking for a INSERT INTO T VALUES (....) solution. The problem is with concatanating values within single quote(').

Let me know if you need me to clarify something which will help you provide solution. Thanks !

6 REPLIES
Enthusiast

Re: Inserting multiple values with single quotes in single field

I did this way:

insert into abc.raja_test1 values('''USA'', ''CHN'', ''IND'', ''GBR''');

I use quote

'USA', 'CHN', 'IND', 'GBR'

Cheers,

Raja

Re: Inserting multiple values with single quotes in single field

Raja, the result in this case will be -   "USA", "CHN", "IND", "GBR". That is not what I need because when I use a IN operator this will fail. 

I need every string to be in single quotes - 'USA', 'CHN', 'IND', 'GBR

Enthusiast

Re: Inserting multiple values with single quotes in single field

Sunny,

Have you tried Raja's solution? I think the double qoutes will not be inserted instead the single qouted string will be added. 

Khurram
Enthusiast

Re: Inserting multiple values with single quotes in single field

Hi Sunny,

The above one I tested it and it works as per your requirement. Test it and see it. You may miss out some quotes. Let me know if you want it otherwise.

Cheers,

Re: Inserting multiple values with single quotes in single field

Better late than never :-) it worked. 

Raja - I thought you used double quotes in your example above. It obviously doesnt work that way. I have realized it a little late that what you used are two consecutive single quotes.

So, in Teradata if you want to include single quote (') in the string, use two consecutive single quotes ('').

Thanks again for your help.

Cheers,

Sundeep 

Senior Apprentice

Re: Inserting multiple values with single quotes in single field

Hi Sundeep,

it's not specifically Teradata, every DBMS works like that, to get a single quote within a string you need to write two single quotes.