Add Single Quotes to Results in a Column from a SQL Query

Add single quotes to results in a column from a SQL query

If you are using SQL SERVER then modify your query according to below :

SELECT opportunities.ref FROM opportunities
CASE WHEN opportunities.ref IS NOT NULL THEN ''''+opportunitiesref+''''
GROUP BY opportunities.ref

Put single quotes into single quotes in SQL Server string

The error you receive is because the statement string is not valid. You end up with an unescaped string.

You need to add an escaped quote ('') for each quote you need, ('''''') like this:

Declare @sql2 varchar(max) =''
declare @tablename2 as varchar(255) ='test2'
select @sql2 = @sql2 + 'update [' + @tablename2 + '] set [' + c.name + ']' + ' = NULL ' +
        ' WHERE ISNULL([' + c.name + '], ' + '''''' + ') = ' + ''''''
from sys.columns c
inner join sys.tables t on c.object_id = t.object_id 
where t.name = @tablename2
EXEC (@sql2)
go 

How to get SQL query result as one row comma delimited and with single quotes?

I am able to get following query to work for me:

Select SUBSTRING( 
( 
SELECT ',' +''''+ ColName +'''' AS 'data()' 
FROM TableName FOR XML PATH('') 
), 2 , 999999) As DCN

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

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

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

Write a query to select columns wrapped in single quotes

While dealing with numerical data, you can simply concatenate. NULL values stay NULL. But for character data (or similar) that might need escaping, use proper functions.

quote_nullable() or quote_literal() - depending on whether you have NULL values:

SELECT quote_nullable(val) AS quoted_val FROM tbl;

Details for quoting:

• Insert text with single quotes in PostgreSQL

How to Include single quote in the STUFF

DECLARE @Xml XML 
DECLARE @Propercase VARCHAR(max) 
DECLARE @delimiter VARCHAR(5) 

SET @delimiter=' ' 


DECLARE @string nvarchar(max)='ABC,DEF,GHI,JKL'
SET @Xml = Cast(( '<String>' 
                  + @string+ '</String>' ) AS XML)

;WITH cte 
     AS (SELECT a.value('.', 'varchar(max)') AS strings 
         FROM   @Xml.nodes('String') AS FN(a))
SELECT @ProperCase = '(' + REPLACE(STUFF((SELECT ','''+ Upper(LEFT(strings, 1)) 
                                   + Upper(Substring(strings, 2, Len(strings))) + ''')'                                   
                            FROM   cte 
                            FOR xml path('')), 1, 1, ''),',',''',''')    

SELECT @ProperCase

Note that all the STUFF really does is drop the leading comma

single vs double quotes in WHERE clause returning different results

Just to expand on Gordon's answer a little. Your first query:

SELECT
  file_name
FROM table
WHERE file_name = "file_name"

In this case, the double quotes are causing the query engine to treat "file_name" as a column identifier, not a value, so that query is functionally the same as:

SELECT
  file_name
FROM table
WHERE file_name = file_name

Obviously (when written that way) the condition is always true, so the full table is returned.

how to add single quote to the end and beginning of value retrieved in select statement

Like in the answer above described, if you only want to concat an ' to a string the syntax is
'''' || string1 || ''''
however, if you want to add a string like "'start of sentence" you can use '''
so:

''' begin of sentence ' || string1 || ' end of sentence '''

results in:

' begin of sentence string1 end of sentence'

Full example could be sth like

SELECT ''''||col1||''', '''|| col2 ||''', '''|| col3||'''' AS OUTPUT FROM (
SELECT 'Joe' as col1, 'Rose' as col2, 'Chandeler' AS col3 FROM dual);

|          OUTPUT          |
+--------------------------+
|'Joe', 'Rose', 'Chandeler'|

How to insert a value that contains an apostrophe (single quote)?

Escape the apostrophe (i.e. double-up the single quote character) in your SQL:

INSERT INTO Person
    (First, Last)
VALUES
    ('Joe', 'O''Brien')
              /\
          right here  

The same applies to SELECT queries:

SELECT First, Last FROM Person WHERE Last = 'O''Brien'

---

The apostrophe, or single quote, is a special character in SQL that specifies the beginning and end of string data. This means that to use it as part of your literal string data you need to escape the special character. With a single quote this is typically accomplished by doubling your quote. (Two single quote characters, not double-quote instead of a single quote.)

Note: You should only ever worry about this issue when you manually edit data via a raw SQL interface since writing queries outside of development and testing should be a rare occurrence. In code there are techniques and frameworks (depending on your stack) that take care of escaping special characters, SQL injection, etc.

Single quote in a query

Use two single quotes: ''

select *, 'INSERT INTO San_Endereco (Endereco_Id, Logradouro_Id, Bairro_Id, CEP, Logradouro, Livre) VALUES  
(''' + CAST(Endereco_Id as varchar) + ''',''' 
+ CAST(Logradouro_Id as varchar) + ''',''' 
+ CAST(Bairro_Id as varchar) + ''',''' 
+ CAST (CEP as varchar) + ''',''' 
+ CAST(Logradouro as varchar) + ''',''' 
+ CAST(Livre as varchar) + ''')'''  as teste 
FROM San_Endereco 

---

Related Topics