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)
 Split AD description into multiple parts

Author  Topic 

pelizdr
Starting Member

5 Posts

Posted - 2010-08-12 : 08:23:14
I am attempting to extrapolate part number and serial number (created by script when PC is added to domain) from the AD description field. We are currently storing this in SQL. See data examples.
Field: addescription (nvarchar(255),null)
User, John Q @ APM-ABI-- [:2241AL9 - SN:L3ACE2Y]
User, Jane Q APM-ABI-- [T61:7661BP6 - SN:L3ABG9D]
I would like to pull the red text and the green text and update to a new field. I assume the colon could be used as a delimiter, but that is about as far as my knowledge goes with breaking this up. I am pretty sure both fields are a fixed 7 characters in length but as you can see, the position can vary widely (username removed for security).

Any assistance (code especially!) would be appreciated!

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-08-12 : 09:29:08
If its always a 7 character fixed lenth, you can try something like this.
--Sample data
declare @t table (addescription nvarchar(255))
insert @t
select 'User, John Q @ APM-ABI-- [:2241AL9 - SN:L3ACE2Y] '
union all select 'User, Jane Q APM-ABI-- [T61:7661BP6 - SN:L3ABG9D] '

--Query
select substring(addescription,charindex(':',addescription) + 1,7)
, reverse(substring(reverse(addescription),charindex(':',reverse(addescription)) - 7,7))
from @t

--Result
PartNumber SerialNumber
---------- ------------
2241AL9 L3ACE2Y
7661BP6 L3ABG9D

Go to Top of Page

pelizdr
Starting Member

5 Posts

Posted - 2010-08-12 : 09:38:01
Brilliant! Thank you vijayisonly! The only issue I have is the garbage data some people have put in.
Go to Top of Page

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-08-12 : 10:03:09
quote:
Originally posted by pelizdr

Brilliant! Thank you vijayisonly! The only issue I have is the garbage data some people have put in.


Thanks.

You can probably try using CASE to get rid of some of the garbage data....like..
select case when charindex(':',addescription) > 0 then
substring(addescription,charindex(':',addescription) + 1,7)
else '' end as PartNumber
, case when charindex(':',addescription) > 0 then
reverse(substring(reverse(addescription),charindex(':',reverse(addescription)) - 7,7))
else '' end as SerialNumber
from @t

Go to Top of Page

pelizdr
Starting Member

5 Posts

Posted - 2010-08-12 : 10:14:12
Not to be a bother, but I do have another quandry... Similar issue.

Sample data:
CN=ABI-259BC7D0,OU=ABI Workstations,OU=ABI,OU=APM,DC=DC,DC=Company,DC=com
CN=ABI-25B64D0D,OU=ABI Workstations,OU=ABI,OU=APM,DC=DC,DC=Company,DC=com
CN=ACU-RFID,OU=SAN Servers,OU=SAN,OU=AFL,DC=DC,DC=Company,DC=com

Need to extract the red data as one field, green as another, need to reference the second and third instance of OU somehow.
Go to Top of Page

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-08-12 : 10:21:26
The value after OU, is it always 3 characters long? (like ABI,SAN etc)
Go to Top of Page

pelizdr
Starting Member

5 Posts

Posted - 2010-08-12 : 10:32:05
looking at the data, it looks like the length is variable for both fields.
Go to Top of Page

ann
Posting Yak Master

220 Posts

Posted - 2010-08-12 : 17:30:00
split your string via the comma, then pull second ou= value
Go to Top of Page

pelizdr
Starting Member

5 Posts

Posted - 2010-08-13 : 07:22:07
thanks, Ann, and how would I do that? FYI, the first field is always three characters, the second one is between 3 & 4.
Go to Top of Page

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-08-13 : 09:59:45
Sorry..couln't respond earlier.

This is at best a hack. You may want to hang around for a while...someone will provide a better solution.
--Sample Data
declare @t table (col1 varchar(200))
insert @t
select 'CN=ABI-259BC7D0,OU=ABI Workstations,OU=ABI,OU=APM,DC=DC,DC=Company,DC=com'
union all select 'CN=ABI-25B64D0D,OU=ABI Workstations,OU=ABI,OU=APM,DC=DC,DC=Company,DC=com'
union all select 'CN=ACU-RFID,OU=SAN Servers,OU=SAN,OU=AFL,DC=DC,DC=Company,DC=com'

--Query
select parsename(replace(replace(col1,'OU=','.'),',.','.'),2) as [col1]
, substring(parsename(replace(replace(col1,'OU=','.'),',.','.'),1),1,charindex(',',parsename(replace(replace(col1,'OU=','.'),',.','.'),1))-1) as [col2]
from @t

--Result
ABI APM
ABI APM
SAN AFL

Go to Top of Page

glendcruz
Yak Posting Veteran

60 Posts

Posted - 2010-08-15 : 20:14:27
select cast(substring(addescription,charindex(':',addescription)+1,
charindex('-',reverse(addescription))-6)as varchar(12))as 'Part No:',
cast(replace(reverse(substring(reverse( addescription),1,
charindex('-',reverse(addescription))-5)),']','')as varchar(15))as 'Serial No:'
from @t
Part No: Serial No:
------------ ---------------
2241AL9 L3ACE2Y
7661BP6 L3ABG9D

(2 row(s) affected)
Go to Top of Page
   

- Advertisement -