Reputation: 29
How to eliminate single quote problem in dynamic sql of SQL server without using replace function
ex:
DECLARE @QRY NVARCHAR(MAX)=''
SET @QRY= @QRY + 'SELECT CONCAT(LAST_NAME,'' ''+FIRST_NAME ) as FullName FROM TEST_TABLE'
Upvotes: 0
Views: 102
Reputation: 269
If you have only one single quote in your firstname and lastname column, you can try the following solution.
CREATE TABLE #Names
(
FirstName VARCHAR(30)
, LastName VARCHAR(30)
);
INSERT INTO #Names
( FirstName, LastName )
VALUES ( 'Mah''esh' -- FirstName - varchar(30)
, 'Panchala' -- LastName - varchar(30)
),
( 'Mah''esh' -- FirstName - varchar(30)
, 'Panchal''a' -- LastName - varchar(30)
),
( 'Mahesh' -- FirstName - varchar(30)
, 'Panchal''a' -- LastName - varchar(30)
);
SELECT *
FROM #Names;
DECLARE @QRY NVARCHAR(MAX)= '';
SET @QRY = 'SELECT CONCAT(FN.FirstName, '' '', FN.LastName) AS FullName FROM
( SELECT CASE WHEN PATINDEX(''%''''%'', FirstName) <> 0
THEN STUFF(FirstName, PATINDEX(''%''''%'', FirstName),
1, '''')
ELSE FirstName
END AS FirstName
, CASE WHEN PATINDEX(''%''''%'', LastName) <> 0
THEN STUFF(LastName, PATINDEX(''%''''%'', LastName),
1, '''')
ELSE LastName
END AS LastName
FROM #Names
) FN';
SELECT @QRY;
EXECUTE (@QRY);
Upvotes: 0
Reputation: 560
Please try the below query with REPLACE.This will help you if I understand you correctly. This is working fine in SQL Server 2012.
IF OBJECT_ID('tempdb..#TEST_TABLE') IS NOT NULL
DROP TABLE #TEST_TABLE
CREATE TABLE #TEST_TABLE
(
LAST_NAME nvarchar(200) NOT NULL,
FIRST_NAME nvarchar(200) NOT NULL
)
INSERT INTO #TEST_TABLE VALUES( 'LastNa''me', 'First''name')
EXEC ('SELECT CONCAT(REPLACE(LAST_NAME,'''''''',''''),'' ''+(REPLACE(FIRST_NAME ,'''''''',''''))) as FullName FROM #TEST_TABLE')
Upvotes: 1