CODEWITHSUNDEEP
sqlsql-server
goofyui
goofyui

Reputation: 3492

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

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',

Upvotes: 9

Views: 45128

Answers (8)

Bhargav Shah
Bhargav Shah

Reputation: 36

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

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

Upvotes: 2

Jiri
Jiri

Reputation: 71

Use this construction

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

return '<MyString>'

Upvotes: 7

user1242496
user1242496

Reputation: 139

If its SQL Server 2017 and above

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

Upvotes: 1

Elias
Elias

Reputation: 11

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,'')

Upvotes: 0

Hari Lakkakula
Hari Lakkakula

Reputation: 307

Use three single quotes to concatenating 

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

Upvotes: 0

LDS
LDS

Reputation: 362

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 + ') ;'

Upvotes: -1

Amit Dwivedi
Amit Dwivedi

Reputation: 31

You can simply concatenate the columns using the query

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

Upvotes: 3

Andrey Korneyev
Andrey Korneyev

Reputation: 26856

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

Upvotes: 6

Related Questions