How to concatenate a column value with single quotes in sql?

Question

How to concatenate a column value with single quotes and a comma in sql?

select tpaa_id  from dbo.Sheet
where tpaa_id is not null

At present query returns, value as ..

ABC123
ABC456

We have around 1000 records.

I expect to return as

'ABC123',
'ABC456',

There are many similar questions and answers here like [this](http://stackoverflow.com/q/2046037/4519059) , Have you checked them? — shA.t, Jul 23 '15 at 13:40

score 7 · Answer 1 · edited Jan 24 '20 at 10:16

7

Use this construction

SELECT CONCAT(CHAR(39), MyString ,CHAR(39)) FROM Table

return '<MyString>'

edited Jan 24 '20 at 10:16

Iñigo

1,877
7
25
55

answered Jan 24 '20 at 08:51

Jiri

71
1
1

score 6 · Answer 2 · answered Jul 23 '15 at 13:42

6

You can use variable for concatenation:

declare @result nvarchar(max)

select @result  = isnull(@result + ', ', '') + '''' + tpaa_id  + '''' 
from dbo.Sheet
where tpaa_id is not null

select @result

answered Jul 23 '15 at 13:42

Andrey Korneyev

26,353
15
70
71

Thank you Andy, Query works .. however i changed nvarchar(max) to 4000 limit – goofyui Jul 23 '15 at 13:44
This syntax is giving me a problem : declare @result nvarchar(max) – goofyui Jul 23 '15 at 13:57
@goofyui what exactly problem are you experiencing with this line of code? – Andrey Korneyev Jul 23 '15 at 13:59

score 2 · Answer 3 · edited Aug 07 '17 at 06:41

2

You can simply concatenate the columns using the query

select ''''||tpaa_id||''''||','  from dbo.Sheet
where tpaa_id is not null;

edited Aug 07 '17 at 06:41

afxentios

2,502
2
21
24

answered Aug 07 '17 at 05:48

Amit Dwivedi

21
2

score 2 · Accepted Answer · answered Mar 10 '20 at 16:18

2

To get the values with comma , use below statement...

select field 1 || ',' from table name;

answered Mar 10 '20 at 16:18

Bhargav Shah

36
1

Thank you Bhargav,I wasn't aware of pipeline syntax in the select query. I will do that. Thank you again :) – goofyui Mar 10 '20 at 16:20

score 1 · Answer 5 · answered Jul 23 '19 at 02:31

1

If its SQL Server 2017 and above

SELECT STRING_AGG(QUOTENAME(tpaa_id,''''), ',') FROM dbo.Sheet

answered Jul 23 '19 at 02:31

user1242496

139
2
8

I have to check the syntax string_agg – goofyui Jul 23 '19 at 14:31

score 0 · Answer 6 · answered Mar 18 '19 at 10:20

0

Use three single quotes to concatenating

select  'insert into [LookUpModeofTransfer] values('''+Name+'''' +',1'+')'  from 
(SELECT  name,isActive FROM [dbo].[LookUpModeofTransfer]
 )a

answered Mar 18 '19 at 10:20

Hari Lakkakula

199
1
4

score 0 · Answer 7 · edited Mar 18 '19 at 10:36

0

when we do String Concatenation in sql we need to use ISNULL function----------------

DECLARE @str1 NVARCHAR(20)
DECLARE @str2 NVARCHAR(20)
SET @str1 = NULL
SET @str2 = 'str2'
select @str1 + @str2
select ISNULL(@str1,'') + ISNULL(@str2,'')

edited Mar 18 '19 at 10:36

hotfix

3,376
20
36

answered Mar 18 '19 at 10:26

Elias

11
2

score -1 · Answer 8 · answered Oct 06 '18 at 13:52

hi 
'UPDATE LinkedMySQLDB.TableToUpdate AS a SET a.MySQLField=''updated'' WHERE a.ID IN (LocalMSSQLDB.LocalTable.ID) ;' 

This is a string constant but you need a value for LocalMSSQLDB.LocalTable.ID.

Declare @ldb int;
set @ldb = select LocalMSSQLDB.LocalTable.ID

'UPDATE LinkedMySQLDB.TableToUpdate AS a SET a.MySQLField='' + value + '' WHERE a.ID IN (' + @ldb + ') ;'

How to concatenate a column value with single quotes in sql?

8 Answers8