issue with single quotes in the column values

Question

issue with single quotes in the column values

asita

SSCarpal Tunnel

Points: 4144
More actions
March 29, 2016 at 10:21 am

#309514

Hello Good Evening,
I have a simple table named clientslist with columns Cust_Id, FirstName, LastName
now I need to generate script with all insert values such as
select 'SELECT ' + '''' + ltrim(rtrim(Cust_id)) +''', ' + ''''+ ltrim(rtrim(LastName)) + ''', ' + '''' + ltrim(rtrim(FirstName)) + '''' + ' UNION ALL' from Cleintslist
the above is working fine but causing issues when a firstname/last name column values contains 'single quites in it.
for example: at this line it is causing issue, when using this code to insert into another table in production. SELECT '621713504', 'D'Souza', 'Justine' UNION ALL
Please advise
Thank you much in advance

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Alan Burstein SSC Guru Points: 61141 More actions · Answer 1

Can you include some DDL and sample data?

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

shaneoneillis SSCommitted Points: 1750 More actions · Answer 2

Have you tried wrapping the columns in the QUOTENAME() function, and then specifying that you want them quoted with apostrophes ('')?

QUOTENAME([column_name], '''')

'SELECT ' + QUOTENAME(LTRIM(RTRIM(ClientList.Cust_Id)), '''') + ', ' + QUOTENAME(LTRIM(RTRIM(ClientList.LastName)), '''') + ', ' + QUOTENAME(LTRIM(RTRIM(ClientList.FirstName)), '''') + ' UNION ALL'

drew.allen SSC Guru Points: 76985 More actions · Answer 3

Use the QUOTENAME function instead of hard-coding the quotes.

I also recommend that you use the XML string concatenator to create a dynamic table value constructor rather than using UNION all. For example,

SELECT 'SELECT * FROM ('

+ STUFF(

(

SELECT ', (', Cust_id AS [text()], ', ', QUOTENAME(ClientList.LastName, ''''),', ', QUOTENAME(ClientList.FirstName, ''''), ')'

FROM ClientList(Cust_id, LastName, FirstName)

FOR XML PATH(''), TYPE

).value('.', 'NVARCHAR(max)'), 1, 1, 'VALUES')

+ ') AS t'

You also don't need to quote integer data and you'll probably be able to drop the LTRIM and RTRIM as well.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 4

Few ways of doing this and one is replacing the apostrophes with double apostrophes.

😎

Quick sample and quick correction on Drew's code.

DECLARE @Cleintslist TABLE

(

Cust_id INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED

,FirstName VARCHAR(50) NOT NULL

,LastName VARCHAR(50) NOT NULL

);

-- Sample data

INSERT INTO @Cleintslist(FirstName,LastName)

VALUES ('Al','A''London')

,('Joe','Angelique''')

,('Rob','C''Morra')

,('Justin','Jah''mi')

,('Ann','Jay''line')

,('Sam','Joe''l')

,('Trish','Ne''Vaeh')

,('Terry','Ny''Zere')

,('Mark','Ta''Nasia');

-- Replace ' with '' in the select statement:

SELECT 'SELECT ' + '''' + ltrim(rtrim(CL.Cust_id)) +''', '

+ ''''+ REPLACE(ltrim(rtrim(CL.LastName)),CHAR(39), CHAR(39) + CHAR(39)) + ''', ' + ''''

+ REPLACE(ltrim(rtrim(CL.FirstName)),CHAR(39), CHAR(39) + CHAR(39)) + '''' + ' UNION ALL'

FROM @Cleintslist CL;

-- Slight correction to Drew's fine code

-- 1. misplaced the column name for t

-- 2. changed .value('(.)[1]', 'NVARCHAR(max)') to .value('(./text())[1]', 'NVARCHAR(max)')

-- many times faster using the text() function.

SELECT 'SELECT * FROM ('

+ STUFF(

(

SELECT ', (', Cust_id AS [text()], ', ', QUOTENAME(ClientList.LastName, ''''),', ', QUOTENAME(ClientList.FirstName, ''''), ')'

FROM @Cleintslist ClientList--(Cust_id, LastName, FirstName)

FOR XML PATH(''), TYPE

).value('(./text())[1]', 'NVARCHAR(max)'), 1, 1, 'VALUES')

+ ') AS t(Cust_id, LastName, FirstName);'