Adding double quotes to varchar values in sql statement

Question

Hi.

How to add double quotes to the output result based on the values?

I have tried this, which works : select quotename(Customer_Name,'""')

However, I do not want to put for each column. But depending on the value of the query output, if the value contains a alphabet, then the result should be added with quotes, if the result is number , hen quotes are not required.

Is this possible to achieve in sql ?

Answer 1

Hi @Padmanabhan, Venkatesh ,

Thank you so much for posting here.

Please refer below and check whether it is helpful to you.

CREATE TABLE Customer  
   (  
   Customer_Name varchar(50) NOT NULL  
   );     
GO     
INSERT INTO Customer values  
('DB Manager12'),  
('IT Director'),  
('3346');  

select IIF(isnumeric(Customer_Name)=1,Customer_Name,quotename(Customer_Name,char(34))) from testdb.dbo.Customer  

BCP command as below:

BCP "select IIF(isnumeric(Customer_Name)=1,Customer_Name,quotename(Customer_Name,char(34))) from testdb.dbo.Customer" queryout "d:\test.txt" -c -t","  -T  

Output:

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Hot issues November--What can I do if my transaction log is full?
Hot issues November--How to convert Profiler trace into a SQL Server table

Answer 2

But depending on the value of the query output

That doesn't make sense to me. You don't want to base your decision on a value in a column but rather the column itself. Imagine the situation where you have a Description column that is typed as VARCHAR. The first row in your resultset has a value of 10 for that column whereas the second row has a value of Bike. You wouldn't want to double quote the second row and not the first as this would mess up attempts to parse the data. If this data were passed to some other code it would have to handle double quotes in all columns even if the column wasn't integral because there is a chance the quotes may or may not be there.

The correct approach, in my opinion, is to look at the column type. If it is textual then wrap it in double quotes. The underlying value in the row isn't relevant. Of course for NULL you'd probably just leave it NULL.

Answer 3

Try something like this too:

select case when try_cast(MyColumn1 as float) is not null then MyColumn1 else quotename(MyColumn1, '"') end
from MyTable

See also ISNUMERIC.

But you can format the data in C# as well.

Answer 4

Is it for a export of data? Then use Import/Export wizard, it as an option as feature to embed text in double quotes

Answer 5

If you want to export data with BCP and want some columns to be quoted, this can be achieved with a format file. The result will be the same across all rows though.

I have an article about using bulk load and format files on my web site; http://www.sommarskog.se/bulkload.html