question

Clint avatar image
Clint asked

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

concatenation
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
3 Likes 3 ·
Its a house rule - no laughing at sensible questions.
0 Likes 0 ·
Kev Riley avatar image
Kev Riley answered

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

try

select '(''' + tagname + ''')' from dbo.tags
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
0 Likes 0 ·
that worked perfectly thank you
0 Likes 0 ·
This is great information and will be adding it to my notes thank you!
0 Likes 0 ·
Oleg avatar image
Oleg answered

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

2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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!!
0 Likes 0 ·
This is great information and will be adding it to my notes thank you!
0 Likes 0 ·
Jeff Moden avatar image
Jeff Moden answered

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;
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Quotename is your friend if building dynamic SQL.
0 Likes 0 ·
This is great information and will be adding it to my notes thank you!
0 Likes 0 ·
You bet. Thanks for the feedback folks.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.