Mahesh Panchala
Mahesh Panchala

Reputation: 29

How to eliminate single quote in dynamic sql of SQL server without using replace function

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

Answers (2)

Drishya1
Drishya1

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

Praveen ND
Praveen ND

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

Related Questions