Parse Data in SQL

[zzfive03]

Does anyone have a suggestion on how to parse a recordset of strings that look like this:

CN=Chris Schoop,OU=Facilities,OU=Bldg 163,OU=HLC NEW,OU=HLC,DC=AD,DC=homeloancenter,DC=com;CN=Obdulia Kerr,OU=Facilities,OU=Bldg 163,OU=HLC NEW,OU=HLC,DC=AD,DC=homeloancenter,DC=com;CN=Susan Hoang,OU=Facilities,OU=Bldg 163,OU=HLC NEW,OU=HLC,DC=AD

I am sure I need to use a cursor, and each rown, I need to extract all of the CN=<USER NAME>. In the example above, there would be 3:

Chris Schoop
Obdulia Kerr
Susan Hoang

What would be a good routene to use to parse out this informaiton?

Thank you for any suggestions.
MH

 

[zzfive03]

Please note, the example string i gave, is actluay a single row (it looks like 4 rows in the display above, but its all 1 row)..

MH

 

[SQLBill]

Are you storing that in SQL Server? Really? If not, then you need to post in the appropriate forum.

If you are using SQL Server, you should have had separate columns for each value. But since that didn't happen, I would suggest using PATINDEX to find where the CN= occurs and then where OU=Facilities occurs. Then grab the SUBSTRING from the first PATINDEX to the second PATINDEX value.

Refer to the BOL for more information on PATINDEX and SUBSTRING.

-SQLBill

BOL=Books OnLine=Microsoft SQL Server's HELP
Installed as part of the Client Tools
Found at Start>Programs>Microsoft SQL Server>Books OnLine

Posting advice: FAQ481-4875

 

[zzfive03]

I got it working... very tough.. but here it is in case someone else wants it..



DECLARE
--@ID int
@Member varchar(255)
-- ,@EmailAddress varchar(255)



declare @separator_position1 int
declare @separator_position2 int
declare @separator_position3 int
declare @separator_position4 int
declare @separator1 varchar(20)
declare @separator2 varchar(20)
declare @separator3 varchar(20)
declare @array_value varchar(1000)
declare @tmpInsert varchar(1000)

DECLARE user_cursor CURSOR
FOR
SELECT TOP 100

[member]

FROM #temp WITH (NOLOCK)

OPEN user_cursor
FETCH NEXT FROM user_cursor INTO @member
WHILE (@@fetch_status <> -1)

BEGIN
IF (@@fetch_status <> -2)
BEGIN
create table #Tmp_UserList (UserName varchar(255))

set @separator1 = 'CN='
set @separator2 = ',OU'
set @separator3 = ','


while patindex('%' + @separator1 + '%' , @member) <> 0
begin


select @separator_position1 = patindex('%' + @separator1 + '%' , @member)
select @separator_position2 = patindex('%' + @separator2 + '%' , @member)
select @separator_position3 = patindex('%' + @separator1 + '%' , @member)
print 'Pos1:' + cast(@separator_position1 as varchar)
print 'Pos2:' + cast(@separator_position2 as varchar)

--select @array_value = left(@member, @separator_position1 + 3)

select @array_value = substring(@member, @separator_position1+3, @separator_position2 - @separator_position1-3)
--insert into #Tmp_UserList( @array_value)
print 'Remaining Memberbefore:' + @member

select @separator_position4 = patindex('%' + @separator3 + '%' , @member)

select @tmpInsert = replace(substring(@member,1,@separator_position4-1),'CN=','')
select @member = stuff(@member, 1, @separator_position2, '')
print 'USERS NAME:' + @tmpInsert

select @member = stuff(@member, 1, @separator_position2, '')

select @separator_position3 = patindex('%' + @separator1 + '%' , @member)
select @member = substring(@member, @separator_position3, len(@member)-@separator_position3)
print 'Remaining MemberAfter:' + @member

-- PRINT 'UserName:' + @array_value


end

drop table #Tmp_UserList


END
FETCH NEXT FROM user_cursor INTO @member
END
CLOSE user_cursor
DEALLOCATE user_cursor

 

[ESquared]

I would have just exported the data to Excel, done some copying and pasting and search-and-replacing (with maybe even a pop over to Word which can handle nonprinting characters in searches), then reimported it to SQL Server. Unless there are more than 65000 rows, it probably would take 10 or 15 minutes. Unless you have to do this on a regular basis. Then I would have used the prefixes and the fact that data is separated by commas and semicolons.

Split at semicolons into new rows.
Split at commas (also splitting at equal signs to determine column) into separate columns.
Voilà!

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)