Home Convert : to comma separated value
Reply: 3

Convert : to comma separated value

aravind
1#
aravind Published in 2017-11-14 12:30:54Z

I came across a row which has multiple values separated by a colon (:). SQL considers the values to be one and throws an error" Cannot fine the requested Privilege". Example of the row shown below.

Servername  Instancename    Databasename Environment userid      access
ITSUSMPW01430   ITSUSMPW01430   ALL        DEV       userid SYSADMIN:SERVERADMIN

Here 2 roles are requested seperated by : Need some assistance on how can we make SQL consider the values different?

Gordon Linoff
2#
Gordon Linoff Reply to 2017-11-14 12:34:03Z

First, you should fix the data structure. Storing multiple values in a single column is a bad idea. Instead, you should have a table, with one row per instance and access (assuming instance is the right entity).

Sometimes you are stuck with other people's really bad design decisions. For merely searching for a single value, you can use like:

where ':'  + access + ':' like '%:' + 'SERVERADMIN' + ':%'

Such a query cannot take advantage of an index and is limited in optimization options. The real solution is to create the second table, as described above.

Yogesh Sharma
3#
Yogesh Sharma Reply to 2017-11-14 12:44:12Z

You could also differentiate them

SELECT Servername,
       Instancename,
       Databasename,
       Environment,
       userid,
       a.value('.', 'VARCHAR(MAX)') [access]
FROM
(
    SELECT Servername,
           Instancename,
           Databasename,
           Environment,
           userid,
           CAST('<A>'+REPLACE(access, ':', '</A><A>')+'</A>' AS XML) AS access 
    FROM <table>
) A
CROSS APPLY access.nodes('/A') AS split(a); 

Result :

Servername      Instancename    Databasename Environment userid   access
ITSUSMPW01430   ITSUSMPW01430   ALL        DEV          userid      SYSADMIN
ITSUSMPW01430   ITSUSMPW01430   ALL        DEV          userid      SERVERADMIN 
Yogesh Sharma
4#
Yogesh Sharma Reply to 2017-11-16 06:34:17Z

I resolved the issue by modifying the below code.

where ':'  + access + ':' like '%:' + 'SERVERADMIN' + ':%'

Since we need to differentiate the roles seperated by : in the same column, I used the below query and it worked successfully for 2 as well as more values.

select access from table1 where access like '%:' and access like ':%';

Thank you Gordon for the pseudo code.

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.417177 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO