x

single quotation mark in a concatenation

Hello all I'm sure I will be laughed out of here but I cant seem to figure out how to do this

I simply want to ad a (' the the begining of a returned query and add a ') to the end

i would like to do something like this

select (' + tagname + ') from dbo.tags

the problem is with the single quotation. I'm almost to the point of creating a table that has this punctuation

Any Ideas

more ▼

asked Apr 22, 2010 at 12:08 PM in Default

Clint gravatar image

Clint
100 6 6 7

It's a great question and one that causes no end of pain. As Kev showed, two single quotes gets read as one when they're inside another pair of single quotes. That can get really confusing, really fast, so this is an excellent question. Thanks for posting it.
Apr 22, 2010 at 01:34 PM Grant Fritchey ♦♦
Its a house rule - no laughing at sensible questions.
Apr 24, 2010 at 07:30 AM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left

3 answers: sort newest

No you won't be laughed out of here - it's a very good question!

try

select '(''' + tagname + ''')' from dbo.tags 
more ▼

answered Apr 22, 2010 at 12:48 PM

Kev Riley gravatar image

Kev Riley ♦♦
52.7k 47 49 76

The basic rule is if you want a single quote inside a string, put two of them. This can in some situations lead to a whole lot of single quotes.
Apr 22, 2010 at 12:56 PM TimothyAWiseman
that worked perfectly thank you
Apr 22, 2010 at 01:00 PM Clint
This is great information and will be adding it to my notes thank you!
Apr 26, 2010 at 11:50 AM Clint
(comments are locked)
10|1200 characters needed characters left

Sorry I'm late to the party but let's not forget the function actually built to accomplish such a thing...

SELECT '(' + QUOTENAME(tagname,'''') + ')' FROM dbo.Tags; 
more ▼

answered Apr 24, 2010 at 01:33 AM

Jeff Moden gravatar image

Jeff Moden
1.7k 2 4 8

Quotename is your friend if building dynamic SQL.
Apr 24, 2010 at 02:11 AM TimothyAWiseman
This is great information and will be adding it to my notes thank you!
Apr 26, 2010 at 11:49 AM Clint
You bet. Thanks for the feedback folks.
May 02, 2010 at 11:06 PM Jeff Moden
(comments are locked)
10|1200 characters needed characters left

Tim already posted a perfect answer where he mentioned that this technique "can lead to a whole bunch of single quotes". The good news is that because the keywords such as select, from etc come in blue color in the SSMS editor, you can immediately see that some quotes are missing in the bunch (when you don't see expected blue color). I personally prefer the bunch of single quotes approach, but if they are not comfortable then there is another technique to simply instruct the engine to print the single quote when needed by specifying its code. For example, in your case, the statement will look like this:

select '(' + char(39) + tagname + char(39) + ')' from dbo.tags;

This will lead to the same result but the inclusion of quotes is easier to see.

Oleg

more ▼

answered Apr 22, 2010 at 02:24 PM

Oleg gravatar image

Oleg
15.9k 2 4 24

Oleg, the colour coding is true until you find a need for dynamic SQL and then the "count the quote" game goes to a whole new level!!
Apr 24, 2010 at 04:06 AM Fatherjack ♦♦
This is great information and will be adding it to my notes thank you!
Apr 26, 2010 at 11:50 AM Clint
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x20

asked: Apr 22, 2010 at 12:08 PM

Seen: 6160 times

Last Updated: Sep 23, 2010 at 03:50 AM