Parse String, Extract Multiple Parts of a Sting

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 2005 Forums

Transact-SQL (2005)

Parse String, Extract Multiple Parts of a Sting

Author

Topic

Amazing40
Starting Member

6 Posts

Posted - 2009-11-19 : 17:00:21

Need query that can parse a string/field in SQL2005
I need to take each of two or three character sets following OU= and put each into a respective Column/field in a temp table..
ex...

LDAP://adag.agaga,OU=audit,OU=NYC,OU=Paris,ad.fafsadfs,hjhkj
LDAP://adagagaga,OU=finance,OU=LA,OU=London,adfafsadfs
LDAP://adagagaga,OU=Marketing,OU=Dublin,adfaf.sadfs

TempTable...
Department Location Location2
audit NYC Paris
finance LA London
Marketing Dublin

I've been trying Parsename with replace and ltrim rtrim, and charindex with substring, but haven't got it. Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-20 : 01:40:46

Play with this
http://sqlblogcasts.com/blogs/madhivanan/archive/2009/11/18/parsing-a-string.aspx

Madhivanan

Failing to plan is Planning to fail

Amazing40
Starting Member

6 Posts

Posted - 2009-11-20 : 11:49:20

Can't seem to get it to work.

Amazing40
Starting Member

6 Posts

Posted - 2009-11-20 : 11:50:33

Any other examples where the parsing goes through a table?

I started with this to remove the data in the string up to and including the first "OU="...

SELECT SUBSTRING([ADPath], CHARINDEX('ou=', [ADPath]) +3, LEN([ADPath]) -
(CHARINDEX('(ou=', [ADPath])+3) )
FROM [IS].[IS].[zATable]

but I'm not sure how to loop through and keep the data, and then get the second and third portions that follow "OU="

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-20 : 12:48:28

How about this?


CREATE TABLE #myTable99(Col1 varchar(50))
GO

INSERT INTO #myTable99(Col1)
--		         1         2         3         4         5         6
--		123456789012345678901234567890123456789012345678901234567890
SELECT 'LDAP://adag.agaga,OU=audit,OU=NYC,OU=Paris,ad.fafsadfs,hjhkj' UNION ALL
SELECT 'LDAP://adagagaga,OU=finance,OU=LA,OU=London,adfafsadfs' UNION ALL
SELECT 'LDAP://adagagaga,OU=Marketing,OU=Dublin,adfaf.sadfs'
GO

SELECT	  CASE 
			WHEN Second_OU = 0 THEN NULL ELSE	
			SUBSTRING(Col1, (First_OU+3) ,(First_Comma-1) -(First_OU+3)+1)  
			END AS FirstValue
		, First_OU
		, First_Comma
		, CASE 
			WHEN Second_OU = 0 THEN NULL ELSE
		    SUBSTRING(Col1, (Second_OU+3),(Second_Comma-1)-(Second_OU+3)+1) 
			END AS SecondValue		 
		, Second_OU
		, Second_Comma
		, CASE 
			WHEN Third_OU = 0 THEN NULL ELSE
		    SUBSTRING(Col1, (Third_OU+3) ,(Third_Comma-1) -(Third_OU+3)+1)  
			END AS ThridValue	 
		, Third_OU
		, Third_Comma
  FROM ( 
	SELECT Col1
		 , First_OU
		 , First_Comma
		 , Second_OU
		 , Second_Comma
		 , CHARINDEX('OU=',Col1, Second_Comma) AS Third_OU
		 , CHARINDEX(',',Col1,CHARINDEX('OU=',Col1, Second_Comma)) AS Third_Comma
	  FROM (
		SELECT Col1
			 , First_OU
			 , First_Comma
			 , CHARINDEX('OU=',Col1, First_Comma) AS Second_OU
			 , CHARINDEX(',',Col1,CHARINDEX('OU=',Col1, First_Comma)) AS Second_Comma
		  FROM (SELECT Col1
					 , CHARINDEX('OU=',Col1) AS First_OU
					 , CHARINDEX(',',Col1,CHARINDEX('OU=',Col1)) AS First_Comma
				  FROM #myTable99
				) AS XXX
			) AS YYY
	) AS ZZZ
GO


DROP TABLE #myTable99
GO

And a blog entry

http://weblogs.sqlteam.com/brettk/archive/2009/11/20/61057.aspx

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Amazing40
Starting Member

6 Posts

Posted - 2009-11-20 : 13:34:59

Wow, I got that to work. Now I'll have to spend quite some time to understand the code.

I also need to insert the results into a non-temp table that is already created along with one other field from the originating table, but not sure where to put that part due to the complexity of the script.
I've not had to do much SQL in over a year, and then it was not that complex.

Thanks!!!

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-20 : 13:49:48

Just Add INSERT INTO <myTable> (Collist)

On top of the SELECT

What it's doing

First it does a select against the table and find the first OU= and its's comma...

That is then used as a derived table, which passes that data up and out to a second second

It uses the offsets in that select to find the second OU=

THAT select is then used as a derived table and passed up and out to another select, using the second occurence offsets in the outter (3rd) select to then find the third occurance of OU=

THEN

We take all of that, with the offsets, and then substring the column to find your values

Kinda like making temp tables on the fly

Understand?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Amazing40
Starting Member

6 Posts

Posted - 2009-11-20 : 15:02:10

Brett,

Looks good indeed, Impressed!!
Thanks Much!! I understand most of what you are saying generally, but as far as the actual syntax... I'll have to keep studying the script.
I was able to add the insert "On Top" as you say, but was not able to pull in another field from the input table (ex. Col2 from originating table, you used #myTable99 created on the fly) and then insert it into the destination permanent table.

See you are from NJ, same here. Right now in Newark working.

Amazing40
Starting Member

6 Posts

Posted - 2009-11-20 : 16:10:20

// Table z_location has four cols. (zdepartment, zschool, zlocation, and zAcctControl) and gets loaded below...
// First three fields are extracted via charindex and substring from Field StringPathIn in ADusers table as seen below
//
// Need to select one more field from ADusers (originating table) and insert into z_location
// Everything I've tried doesn't succeed.

TRUNCATE TABLE [IT].[dbo].[z_ Location]
INSERT INTO [IT].[dbo].[z_Location](zdepartment, zschool, zlocation)

SELECT CASE
WHEN Second_OU = 0 THEN NULL ELSE
SUBSTRING([StringPathIn], (First_OU+3) ,(First_Comma-1) -(First_OU+3)+1)
END AS Department
, CASE
WHEN Second_OU = 0 THEN NULL ELSE
SUBSTRING([StringPathIn], (Second_OU+3),(Second_Comma-1)-(Second_OU+3)+1)
END AS School

, CASE
WHEN Third_OU = 0 THEN NULL ELSE
SUBSTRING([StringPathIn], (Third_OU+3) ,(Third_Comma-1) -(Third_OU+3)+1)
END AS Location
FROM (
SELECT [StringPathIn]
, First_OU
, First_Comma
, Second_OU
, Second_Comma
, CHARINDEX('OU=',[StringPathIn], Second_Comma) AS Third_OU
, CHARINDEX(',',[StringPathIn],CHARINDEX('OU=',[StringPathIn], Second_Comma)) AS Third_Comma

FROM (
SELECT [StringPathIn]
, First_OU
, First_Comma
, CHARINDEX('OU=',[StringPathIn], First_Comma) AS Second_OU
, CHARINDEX(',',[StringPathIn],CHARINDEX('OU=',[StringPathIn], First_Comma)) AS Second_Comma
FROM (SELECT [StringPathIn]
, CHARINDEX('OU=',[StringPathIn]) AS First_OU
, CHARINDEX(',',[StringPathIn],CHARINDEX('OU=',[StringPathIn])) AS First_Comma

FROM [IT].[IT].[ADusers]
where [ACCT_Control] >= 55000
) AS XXX
) AS YYY
) AS ZZZ
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources