向 SQL 查询的列中的结果添加单引号
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/43066545/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Add single quotes to results in a column from a SQL query
提问by Sammy Sung
So I currently have the following SQL:
所以我目前有以下 SQL：
SELECT opportunities.ref FROM opportunities GROUP BY opportunities.ref
which returns the following results:
它返回以下结果：
OP10
OP252
OP52905
OP42
I am trying to achieve the following:
我正在努力实现以下目标：
'OP10'
'OP252'
'OP52905'
'OP42'
I have tried the following:
我尝试了以下方法：
SELECT opportunities.ref FROM opportunities
CASE WHEN opportunities.ref IS NOT NULL THEN "'"+opportunitiesref+"'"
GROUP BY opportunities.ref
but this does not work.
但这不起作用。
I do not want to update the column, or use any @declare functions as this. I am currently using SQL server 2008. Im not sure how to go about this. Help?
我不想更新列，也不想使用任何 @declare 函数。我目前正在使用 SQL Server 2008。我不知道如何解决这个问题。帮助？
回答by Gaurav Rajput
If you are using SQL SERVER then modify your query according to below :
如果您使用的是 SQL SERVER，请根据以下内容修改您的查询：
SELECT opportunities.ref FROM opportunities
CASE WHEN opportunities.ref IS NOT NULL THEN ''''+opportunitiesref+''''
GROUP BY opportunities.ref
回答by Kiran Ukande
If you are using SQL SERVER then refer below queries. it will work.
如果您使用的是 SQL SERVER，请参考以下查询。它会起作用。
1)
1)
SELECT opportunities.ref,concat('''',opportunities.ref,'''')
FROM opportunities
GROUP BY opportunities.ref
2)
2)
SELECT opportunities.ref,
CASE WHEN opportunities.ref IS NOT NULL THEN ''''+opportunities.ref+'''' END
FROM opportunities
GROUP BY opportunities.ref
回答by Samrat Sarang
In case ref column is an integer, you can use
如果 ref 列是整数，则可以使用
SELECT CASE
WHEN opportunities.ref IS NOT NULL
THEN '''' + CONVERT(VARCHAR(MAX), ref) + ''''
END
FROM opportunities
GROUP BY opportunities.ref;
In case you need the entire converted column in a single string separated by a comma
如果您需要以逗号分隔的单个字符串中的整个转换列
DECLARE @listStr VARCHAR(MAX)= '';
DECLARE @result VARCHAR(MAX);
IF EXISTS
(
SELECT 1
FROM opportunities
)
BEGIN
SELECT @listStr = @listStr + tbl_opportunities.ref + ','
FROM
(
SELECT '''' + CONVERT(VARCHAR(MAX), ref) + '''' AS MarketSeq
FROM opportunities
) tbl_opportunities;
SELECT @result = SUBSTRING(@listStr, 1, LEN(@listStr) - 1);
END;
ELSE
BEGIN
SET @result = '';
END;
SELECT @result;
回答by Bharat
Have you tried using this escape sequence \".Usually we do this to escape from unwanted quotes.In your case this works well.
您是否尝试过使用此转义序列 \"。通常我们这样做是为了逃避不需要的引号。在您的情况下，这很有效。
回答by P.Salmon
I can never remember the escape sequence so I tend to use the ascii code
我永远不记得转义序列，所以我倾向于使用 ascii 代码
declare @opportunities table (ref varchar(10))
insert into @opportunities values
('op10'),('op10'),('op10'),
('op20'),('op254')
SELECT
CASE WHEN ref IS NOT NULL THEN concat(char(39),ref,char(39))
else ref
end ref
FROM @opportunities
GROUP BY ref
回答by Rupesh Pandey
Try this
尝试这个
SELECT CASE WHEN opportunities.ref IS NOT NULL THEN ''''+CAST(opportunities.ref as varchar)+'''' END as 'ColumnName' FROM opportunities GROUP BY opportunities.ref
回答by Neethu
For Postgres, you can do the following.
对于 Postgres，您可以执行以下操作。
SELECT
''''|| p.billto_name_first ||''''
FROM
payments p
WHERE
id = $id
