SQLTeam.com | Weblogs | Forums

Concatenating attributes and separate each with a single quote

sql2012

#1

Hi,

Looking to list database names from a SQL server instance on this way

'abc','def', 'xyz'

I will take that result and put it on a NAME IN () statement, inside the parenthesis I mean.

I tried this, but I am but still missing one single quote:

DECLARE @DBList VARCHAR(MAX)
SELECT @DBList = COALESCE(@DBList + ''',', '') + name
FROM sys.databases
ORDER BY name
SELECT @DBList 

That adds the 2nd single quote, but not the initial one.

Any ideas how can I accomplish this?


#2

Add one more quote after the comma like this:

DECLARE @DBList VARCHAR(MAX)
SELECT @DBList = COALESCE(@DBList + ''',''', '') + name
FROM sys.databases
ORDER BY name
SELECT @DBList

But that still leaves the beginning and ending quotes. Perhaps this might be simpler.

DECLARE @DBList VARCHAR(MAX)
SELECT @DBList =(
SELECT STUFF
(
	(SELECT ',''' + name + '''' FROM sys.databases ORDER BY name FOR XML PATH('')
),1,1,''));
SELECT @dblist;

#3

Cool... thanks James

I may try the 2nd option. I am just not familiarized with STUFF and FOR XML PATH, so I need to check BOL.

For the 1st one, which is an improved of mine, maybe this will fix it:

SELECT ''''+ @DBList + ''''

Thanks for reply!


#4

Might be worth you explaining what the underlying problem is that you are working to solve. Creating a delimited list and using IN, and thus using Dynamic SQL, rarely has a problem for which it is the the best solution :smile:

Can you not, for example, do:

SELECT A, B, C, ...
FROM MyTable
WHERE MyColumn IN
    (
        SELECT name
        FROM   sys.databases
    )

or some variation thereof?


#5

That may be a valid option too.

I wanted to learn how to do it the other way though, because after a few min, I got frustrated of not being able to add the starting single quote, lol.

I have not tested it, but yours maybe less overkill.

The initial suggestion or the use of single quotes came from a modification I made of Ola Hallengren code, which requires singles quotes for the dblist.


#6

There are other issues using dynamic SQL with a delimited list.

What happens if a database name has a single-quote in it? Unlikely, I know, but can happen ...

if you broaden this to also use Dynamic SQL for stuff where a User provides some data / parameters etc. then there is the whole SQL Injection can-of-worms to consider ...


#7

I think you're going well beyond what I asked :slight_smile: ... but thanks.

The code is for my own usage and not subject to SQL injection. No worries, I got what I need.


#8

Indeed :smile:

But IME it is common for folk to propose a solution to a problem and ask for help to make that solution work, rather than to describe the problem and seek suggestions as to the best solution ...

Using the later approach, and assuming you get several replies with different solution, is more likely to broaden ones' knowledge.