Substring Replacement
-
Hello All,
I have a table that the column contains a mapi email address that I need to alter.
The /cn=Value needs to be removed, the issue is that value is different on many as is the last /cn=
Current values:MAPI:{Jones, Doug}EX:/o=Company Name/ou=Data Center/cn=Value/cn=dojones
Need to be: MAPI:{Jones, Doug}EX:/o=Company Name/ou=Data Center/cn=dojones
The script that I have written, I am missing something small and walls are padded.... 🙂
select
"_EMAILID_",
(substring([_EMAILID_],1,charindex ( 'EX',[_EMAILID_])-1))+
'EX:/o=Company Name/ou=Data Center'+ (substring([_EMAILID_],charindex ( '/cn',[_EMAILID_])+1 ,len([_EMAILID_])))
FROM _CUSTOMER_
WHERE CLIENT ='DJONES'
Results:MAPI:{Jones, Doug}EX:/o=Company Name/ou=Data Centercn=Value/cn=dojones
Any help would be so greatly appreciated.
Doug
-
I fixed it.
select
"_EMAILID_",
(substring([_EMAILID_],0,charindex ( 'EX',[_EMAILID_])))
+'EX:/o=Company/ou=Data Center'+
+(substring([_EMAILID_],charindex ( '/cn',[_EMAILID_])+10 ,len([_EMAILID_])))
FROM _CUSTOMER_
WHERE CLIENT ='DJONES'
-
Correction: Still in search of answer.......
If anyone could be so kind
Doug
-
Why not just:
UPDATE YourTable
SET YourCol = REPLACE(YourCol, '/cn=Value', '')
--
Adam Machanic
whoisactive -
Adam,
Thanks, I have tried that and I will need about 20-30 separate scripts to do it. Time wise, I would have been done doing it that way, but now it is the principle of the matter.
Doug
-
I don't understand; are there cases in which that doesn't work? Can you post more sample data and sample output?
--
Adam Machanic
whoisactive -
Adam,
Current Sample Data:
MAPI:{Cortez, Jorge}EX:/o=Company Name/ou=Data Center/cn=Recipients/cn=JCortez
MAPI:{Slafka, Andrew}EX:/o=Company Name/ou=Data Center/cn=Houston - WLY/cn=ASlafka
MAPI:{Elliott, Kenneth}EX:/o=Company Name/ou=Houston 2/cn=Recipients/cn=KElliott
MAPI:{Murphy, Reginald}EX:/o=Company Name/ou=Houston 2/cn=Recipients/cn=RRmurphy
MAPI:{Moody, Rice}EX:/o=Company Name/ou=Houston 2/cn=Recipients/cn=RMoody
MAPI:{Tomlin, Lionel}EX:/o=Company Name/ou=Houston 2/cn=Recipients/cn=ltomlin
MAPI:{Desoto, Lynn}EX:/o=Company Name/ou=Houston WLY/cn=Recipients/cn=LDeSoto
MAPI:{Whitmire, Jeff}EX:/o=Company Name/ou=Data Center/cn=Recipients/cn=JDWhitmire
MAPI:{Bauer, James}EX:/o=Company Name/ou=Data Center/cn=Recipients/cn=JBauer
MAPI:{Kenyon, Duane}EX:/o=Company Name/ou=Houston WLY/cn=Recipients/cn=DKenyon
MAPI:{Vandermeulen, Steve}EX:/o=Company Name/ou=Data Center/cn=Recipients/cn=SVandermeulen
MAPI:{Aa, Tonny van der}EX:/o=Company Name/ou=United Kingdom/cn=Etten-Leur - Netherlands/cn=tvanderaa
MAPI:{East, Greg}EX:/o=Company Name/ou=Houston WLY/cn=Recipients/cn=GEast
MAPI:{Hatch, John}EX:/o=Company Name/ou=Data Center/cn=Recipients/cn=JHatch
There actually are two things that need replacing after reviewing this:
Correctly is:
MAPI:{Cortez, Jorge}EX:/o=Company Name/ou=Data Center/cn=Recipients/cn=JCortez
Needing Changes:
MAPI:{Aa, Tonny van der}EX:/o=Company Name/ou=United Kingdom/cn=Etten-Leur - Netherlands/cn=tvanderaa
MAPI:{East, Greg}EX:/o=Company Name/ou=Houston WLY/cn=Recipients/cn=GEast
-
My latest changes have done loads of good, but I found another error.
Script:
select "_EMAILID_",(substring([_EMAILID_],1,charindex('EX:',[_EMAILID_])- 1)+
'EX:/o=Company/ou=Data Center/cn=Recipients/cn='+
substring([_EMAILID_],charindex('s/cn',[_EMAILID_])+5 ,len([_EMAILID_])))
--(substring([_EMAILID_],charindex ( '/ou',[_EMAILID_])+1 ,len([_EMAILID_])))
from _CUSTOMER_
where "_EMAILID_" like 'MAPI%' and
"_INACTIVE_:"=0
Glitch:
Current Value: MAPI:{Vaughn, Rickey}EX:/o=Company Name/ou=Data Center/cn=Amelia ICO - La/cn=RVaughn
Problem: MAPI:{Vaughn, Rickey}EX:/o=Company/ou=Data Center/cn=Recipients/cn=:{Vaughn, Rickey}EX:/o=Company Name/ou=Data Center/cn=Amelia ICO - La/cn=RVaughn
-
Doug,
Probably because I haven't worked with MAPI, you've totally lost me. Can you post exactly what needs to be changed in which strings and by what logic?
--
Adam Machanic
whoisactive -
Adam,
Currently Have
MAPI:{East, Greg}EX:/o=val1/ou=val2/cn=val3/cn=val4
Need to make
val2=Data Center val3=Recipients
Keeping val1 and 4 the same
Easier?
Doug
-
Does this help:
declare @somevalue varchar(200)
set @somevalue = 'MAPI:{East, Greg}EX:/o=val1/ou=val2/cn=val3/cn=val4'
declare @val3 varchar(20)
set @val3 = 'first replace'
declare @val4 varchar(20)
set @val4 = 'second replace'
SELECT stuff(@somevalue, charindex('/cn', @somevalue) + 4, len(@somevalue), @val3)
+ '/cn=' + @val4
--
Adam Machanic
whoisactive -
Adam,
Yes and no.
Because the Val2 and 3, may be diffent lengths, that I will just do an update from the mail server based upon the id field.
My head is tired.
Many Many Many thanks.
Doug
-
If the business logic is the one you wrote in your last post, try something like:
declare @txt varchar(255)
set @txt='MAPI:{Jones, Doug}EX:/o=Company Name/ou=Data Center/cn=Value/cn=dojones'
select charindex('/cn=Value',@txt),
(substring(@txt,0,charindex ( '/ou',@txt)))
+'/ou=Data Center/cn=Recipients/cn='+
+(substring(@txt,charindex ( '/cn',@txt,charindex('/cn', @txt)+1) +4,len(@txt)))
Will return:
MAPI:{Jones, Doug}EX:/o=Company Name/ou=Data Center/cn=Recipients/cn=dojones
For another string:
declare @txt varchar(255)
set @txt='MAPI:{Vaughn, Rickey}EX:/o=Company Name/ou=Data Center/cn=Amelia ICO - La/cn=RVaughn '
select
(substring(@txt,0,charindex ( '/ou',@txt)))
+'/ou=Data Center/cn=Recipients/cn='+
+(substring(@txt,charindex ( '/cn',@txt,charindex('/cn', @txt)+1) +4,len(@txt)))
Will return:
MAPI:{Vaughn, Rickey}EX:/o=Company Name/ou=Data Center/cn=Recipients/cn=RVaughn
I hope it helps
Gabriela
-
Doug,
Quick knock together, seems to work fine for what you are trying to accomplish but needs tidying up.
DECLARE @SectionToChange varchar(2),
@MAPIString varchar(8000),
@StartPos int,
@EndPos int,
@DataLength int,
@ReplacementData varchar(500)
SET @MAPIString = 'MAPI:{Cortez, Jorge}EX:/o=Company Name/ou=Data Center/cn=Recipients/cn=JCortez'
SET @SectionToChange = 'ou'
SET @ReplacementData = 'Steves Replacement Data'
SET @StartPos = (CHARINDEX(@SectionToChange, @MAPIString, 1)+3)
SET @EndPos = CHARINDEX('/', @MAPIString, @StartPos)
IF @EndPos <> 0
BEGIN
SET @DataLength = @EndPos - @StartPos
SET @MAPIString = STUFF(@MAPIString, @StartPos, @DataLength, @ReplacementData)
END
SELECT @MAPIString
Have a play with it in query analyzer and butcher it to make it suit your requirements.
Have fun
Steve
We need men who can dream of things that never were.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply