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 datadeclare @t table (addescription nvarchar(255))insert @tselect 'User, John Q @ APM-ABI-- [:2241AL9 - SN:L3ACE2Y] 'union all select 'User, Jane Q APM-ABI-- [T61:7661BP6 - SN:L3ABG9D] '--Queryselect substring(addescription,charindex(':',addescription) + 1,7), reverse(substring(reverse(addescription),charindex(':',reverse(addescription)) - 7,7))from @t--ResultPartNumber SerialNumber---------- ------------2241AL9 L3ACE2Y7661BP6 L3ABG9D |
|
|
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. |
|
|
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 thensubstring(addescription,charindex(':',addescription) + 1,7)else '' end as PartNumber, case when charindex(':',addescription) > 0 thenreverse(substring(reverse(addescription),charindex(':',reverse(addescription)) - 7,7))else '' end as SerialNumberfrom @t |
|
|
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=comCN=ABI-25B64D0D,OU=ABI Workstations,OU=ABI,OU=APM,DC=DC,DC=Company,DC=comCN=ACU-RFID,OU=SAN Servers,OU=SAN,OU=AFL,DC=DC,DC=Company,DC=comNeed to extract the red data as one field, green as another, need to reference the second and third instance of OU somehow. |
|
|
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) |
|
|
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. |
|
|
ann
Posting Yak Master
220 Posts |
Posted - 2010-08-12 : 17:30:00
|
split your string via the comma, then pull second ou= value |
|
|
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. |
|
|
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 Datadeclare @t table (col1 varchar(200))insert @tselect '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'--Queryselect 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--ResultABI APMABI APMSAN AFL |
|
|
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 @tPart No: Serial No:------------ ---------------2241AL9 L3ACE2Y 7661BP6 L3ABG9D (2 row(s) affected) |
|
|
|
|
|