Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Analyzer
Posting Yak Master
115 Posts |
 Posted - 2013-07-23 : 05:28:28
|
| Hi. Performing security audit using command to retrieve data from Active Director about security groups and drop results into local tbl for analysis.EXEC xp_cmdshell 'net group "AnalyticsDev" /domain' Problem is the col created to store result is varchar(1000) and can hold 1-3 values (loginIDs) per row with lots of trailing/white space.E.g. (EmpID101, EmpID250 EmpID10)Is there a technique to extract the needed value (loginIDs) from col?Thanks in advance |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-07-23 : 06:30:19
|
quote:
Originally posted by visakh16
yep there'sseehttp://visakhm.blogspot.com/2010/02/parsing-delimited-string.htmlhttp://visakhm.blogspot.com/2013/01/delimited-string-split-xml-parsing.html------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
Using while loops for splitting strings are usually inefficient if you have even moderately large amounts of data. Constructing an intermediate XML for this purpose is just awful from a performance perspective.I would recommend Jeff Moden's delimitedSplit8K which can be found here in Fig. 21: http://www.sqlservercentral.com/articles/Tally+Table/72993/ |
 |
|
|
Analyzer
Posting Yak Master
115 Posts |
Posted - 2013-07-23 : 06:40:21
|
| Hi. First thank I managed to parse the strings.Another less techy method would be import .rpt into Excel, separate values into columns, re-import back into SQL TableAppreciate your helpMartin |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-23 : 07:35:48
|
quote:
Originally posted by Analyzer
Hi. First thank I managed to parse the strings.Another less techy method would be import .rpt into Excel, separate values into columns, re-import back into SQL TableAppreciate your helpMartin
Yep...thats also an option but would involve some manual effort in separating valuesThe given suggestions are all programmatically doing the same thing------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
How to get values from Col with trailing spaces