Insert double quotes into SQL output
After I run a query and view the output, for example
select * from People
My o开发者_如何学运维utput is as follows
First Last Email
Ray Smith raysmith@whatever.itis
How would I export this data so that it looks as follows?
"Ray","Smith","raysmith@whatever.itis"
Or is there a way to do this within SQL to modify records to contain quotes?
Because when you export, it's going to include the commas anyway, right?
If the columns you're interested in are 128 characters or less, you could use the QUOTENAME function. Be careful with this as anything over 128 characters will return NULL
.
SELECT QUOTENAME(First, '"'), QUOTENAME(Last, '"'), QUOTENAME(Email, '"')
FROM People
select '"'+first+'","'+last+'","'+email+'"'
from people
This is the kind of thing best done in code however, you shouldn't query for presentation.
select concat(“\"”,first,“\"”,“\"”,Last,“\"”,“\"”,Email,“\"”) as allInOne
Modifying the records to contain quotes would be a disaster; you don't use the data only for export. Further, in theory you'd have to deal with names like:
Thomas "The Alley Cat" O'Malley
which presents some problems.
In Standard SQL, you'd use doubled-up single quotes to enclose single quotes (with no special treatment for double quotes):
'"Thomas "The Alley Cat" O''Malley"'
Some DBMS allow you to use double quotes around strings (in Standard SQL, the double quotes indicate a 'delimited identifier'; SQL Server uses square brackets for that), in which case you might write the string as:
"""Thomas ""The Alley Cat"" O'Malley"""
Normally, though, your exporter tools provide CSV output formatting and your SQL statement does not need to worry about it. Embedded quotes make anything else problematic. Indeed, you should usually not make the DBMS deal with the formatting of the data.
This worked best for me
SELECT 'UPDATE [dbo].[DirTree1] SET FLD2UPDATE=',QUOTENAME(FLD2UPDATE,'''')
+' WHERE KEYFLD='+QUOTENAME(KEYFLD,'''')
FROM [dbo].[Table1]
WHERE SUBSTRING(FLD2UPDATE,1,2) = 'MX'
order by 2
If you are using MS SQL Server, try something like:
SELECT '"'||Table.Column||'"'
FROM Table
-- Note that the first 3 characters between "SELECT" and "||" are: ' " '
-- The characters are the same after "||" at the end... that way you get a " on each side of your value.
精彩评论