SQL Query Help splitting a record into 4 columns

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.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

SQL Query Help splitting a record into 4 columns

Author

Topic

sqldba20
Posting Yak Master

183 Posts

Posted - 2012-10-26 : 11:17:19

Folks:

I need help with a query. I have 1 column in a table which has data in this format (it's the folder structure). I would like to seperate the data into 4 different columns.

\\SERVERNAME\FLDR1\Clients\BFE\Accounts
\\SERVERNAME\FLDR1\Clients\APHE\Accounts
\\SERVERNAME\FLDR1\Vendors\APHE\Location
\\SERVERNAME\FLDR1\Clients\BFE\Location
\\SERVERNAME\FLDR1\Clients\PRUD\Accounts
\\SERVERNAME\FLDR1\Vendors\CDTS\Location
\\SERVERNAME\FLDR1\Vendors\BLMG\Location
\\SERVERNAME\FLDR1\Clients\BFE\Domain
\\SERVERNAME\FLDR1\Vendors\TPHE\Accounts
\\SERVERNAME\FLDR1\Clients\TPHE\Domain

Seperate into 4 columns:

The \\SERVERNAME\FLDR1\ in a seperate column
The \\Clients\ , \\Vendors\ in a seperate column
The \BFE\, \APHE\, \PRUD\ in a seperate column
The \Accounts, \Location, \Domain in a seperate column

Thanks !

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-26 : 11:49:22

Copy the string splitter function from this web page, fig 21, run it to install it.

http://www.sqlservercentral.com/articles/Tally+Table/72993/

Then use the function like in the example below. If you need to add a few extra slashes, add them like I have done for the second column.

CREATE TABLE #tmp (col1 VARCHAR(255));
INSERT INTO #tmp VALUES 
('\\SERVERNAME\FLDR1\Clients\BFE\Accounts'),
('\\SERVERNAME\FLDR1\Clients\APHE\Accounts'),
('\\SERVERNAME\FLDR1\Vendors\APHE\Location'),
('\\SERVERNAME\FLDR1\Clients\BFE\Location'),
('\\SERVERNAME\FLDR1\Clients\PRUD\Accounts'),
('\\SERVERNAME\FLDR1\Vendors\CDTS\Location'),
('\\SERVERNAME\FLDR1\Vendors\BLMG\Location'),
('\\SERVERNAME\FLDR1\Clients\BFE\Domain'),
('\\SERVERNAME\FLDR1\Vendors\TPHE\Accounts'),
('\\SERVERNAME\FLDR1\Clients\TPHE\Domain');

SELECT
	col1,
	'\\'+[3]+'\'+[4],
	[5],
	[6],
	[7]
FROM
	#tmp
	CROSS APPLY  dbo.DelimitedSplit8K(col1,'\')
PIVOT
	(MAX(item) FOR itemnumber IN ([1],[2],[3],[4],[5],[6],[7]))P;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources