REPLACE query help plz!

Hello!
I am posting this problem again. I have gotten somebody's help with the
right solution but I have one more case to work on. I would appreciate
your time and help on this.
create table #temp
(ID int, Cust varchar(80))
insert into #temp values(23, 'Name: abcd DBX: abcdefgh Addr1: 1234')
insert into #temp values(27, 'Name: xyz DBX: xyz Addr1: 9999')
insert into #temp values(25, 'Name: lmn DBX: opqr Addr1: 1234')
-- Case 1: Put CENSORED between DBX: and Addr1: in the Cust column...IT
WORKS!!!
select Col1 = Left(Cust, charindex('DBX:', Cust) + 3) +
replace(substring(Cust, charindex('DBX:', Cust) + 4, LEN(Cust)),
substring(Cust, charindex('DBX:', Cust) + 4,
charindex('Addr1:', Cust) - charindex('DBX:', Cust)-4), ' CENSORED ')
from #temp
-- Case 2: Put CLASSIFIED between Name: and DBX: in the Cust column..IT
DOES NOT WORK for 'xyz' case bc 'xyz is in two places. I need it to
replace only one time which is in bw Name: and DBX: and leave the other
one untouched. how do I do that?
select Col2 = replace(Cust, substring(Cust, charindex(':', Cust)+2,
charindex('DBX:', Cust)- charindex(':', Cust)-2), ' CLASSIFIED ')
from #temp
My SQL for Col2 gives me this:
Name: CLASSIFIED DBX: abcdefgh Addr1: 1234
Name: CLASSIFIED DBX: CLASSIFIED Addr1: 9999
Name: CLASSIFIED DBX: opqr Addr1: 1234
And I want this..
Name: CLASSIFIED DBX: abcdefgh Addr1: 1234
Name: CLASSIFIED DBX: xyz Addr1: 9999
Name: CLASSIFIED DBX: opqr Addr1: 1234
Thanks for your help!
*** Sent via Developersdex http://www.examnotes.net ***Test,
Try:
SELECT STUFF(CUST,1,CHARINDEX(' ',CUST,CHARINDEX(' ',CUST)+1),'CLASSIFIED ')
FROM #TEMP
HTH
Jerry
"Test Test" <farooqhs_2000@.yahoo.com> wrote in message
news:%23irrn3l1FHA.1852@.TK2MSFTNGP10.phx.gbl...
> Hello!
> I am posting this problem again. I have gotten somebody's help with the
> right solution but I have one more case to work on. I would appreciate
> your time and help on this.
> create table #temp
> (ID int, Cust varchar(80))
> insert into #temp values(23, 'Name: abcd DBX: abcdefgh Addr1: 1234')
> insert into #temp values(27, 'Name: xyz DBX: xyz Addr1: 9999')
> insert into #temp values(25, 'Name: lmn DBX: opqr Addr1: 1234')
>
> -- Case 1: Put CENSORED between DBX: and Addr1: in the Cust column...IT
> WORKS!!!
> select Col1 = Left(Cust, charindex('DBX:', Cust) + 3) +
> replace(substring(Cust, charindex('DBX:', Cust) + 4, LEN(Cust)),
> substring(Cust, charindex('DBX:', Cust) + 4,
> charindex('Addr1:', Cust) - charindex('DBX:', Cust)-4), ' CENSORED ')
> from #temp
> -- Case 2: Put CLASSIFIED between Name: and DBX: in the Cust column..IT
> DOES NOT WORK for 'xyz' case bc 'xyz is in two places. I need it to
> replace only one time which is in bw Name: and DBX: and leave the other
> one untouched. how do I do that?
> select Col2 = replace(Cust, substring(Cust, charindex(':', Cust)+2,
> charindex('DBX:', Cust)- charindex(':', Cust)-2), ' CLASSIFIED ')
> from #temp
> My SQL for Col2 gives me this:
> Name: CLASSIFIED DBX: abcdefgh Addr1: 1234
> Name: CLASSIFIED DBX: CLASSIFIED Addr1: 9999
> Name: CLASSIFIED DBX: opqr Addr1: 1234
> And I want this..
> Name: CLASSIFIED DBX: abcdefgh Addr1: 1234
> Name: CLASSIFIED DBX: xyz Addr1: 9999
> Name: CLASSIFIED DBX: opqr Addr1: 1234
> Thanks for your help!
>
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Thanks Jerry. It is not perfectly working:
1. Your SQL is also deleting 'Name:' which I want to keep it. But that
can be fixed by adding 'Name: ' in the SQL i.e.
SELECT 'Name: ' + STUFF(CUST,1,CHARINDEX(' ',CUST,CHARINDEX('
',CUST)+1),'CLASSIFIED ')
FROM #TEMP
2. The main issue is there can a space in the name field (it will not
always be a continuation). So, the whole name needs to be replaced with
CLASSIFIED. Your SQL is not currently handling this.
Example: Insert these two records in the #temp table:
insert into #temp values(25, 'Name: xxx yyy DBX: xxx yyy Addr1: 1234')
insert into #temp values(25, 'Name: ijk 123 DBX: aaa Addr1: 1234')
SELECT 'Name: ' + STUFF(CUST,1,CHARINDEX(' ',CUST,CHARINDEX('
',CUST)+1),'CLASSIFIED ')
FROM #TEMP
The result is:
Name: CLASSIFIED yyy DBX: xxx yyy Addr1: 1234
Name: CLASSIFIED 123 DBX: aaa Addr1: 1234
The corerct result would be:
Name: CLASSIFIED DBX: xxx yyy Addr1: 1234
Name: CLASSIFIED DBX: aaa Addr1: 1234
*** Sent via Developersdex http://www.examnotes.net ***|||Test,
Mod the code to use the : after DBX (then count back 3) for the end number
and change the start postion in the STUFF statment to ensure Name: is
retained. I just had to reboot my machine so I lost all of the code i had
for this. Let me know if you don't get it working and I'll redo and post
the solution.
HTH
Jerry
"Test Test" <farooqhs_2000@.yahoo.com> wrote in message
news:uOj4Pom1FHA.2792@.tk2msftngp13.phx.gbl...
> Thanks Jerry. It is not perfectly working:
> 1. Your SQL is also deleting 'Name:' which I want to keep it. But that
> can be fixed by adding 'Name: ' in the SQL i.e.
> SELECT 'Name: ' + STUFF(CUST,1,CHARINDEX(' ',CUST,CHARINDEX('
> ',CUST)+1),'CLASSIFIED ')
> FROM #TEMP
> 2. The main issue is there can a space in the name field (it will not
> always be a continuation). So, the whole name needs to be replaced with
> CLASSIFIED. Your SQL is not currently handling this.
> Example: Insert these two records in the #temp table:
> insert into #temp values(25, 'Name: xxx yyy DBX: xxx yyy Addr1: 1234')
> insert into #temp values(25, 'Name: ijk 123 DBX: aaa Addr1: 1234')
> SELECT 'Name: ' + STUFF(CUST,1,CHARINDEX(' ',CUST,CHARINDEX('
> ',CUST)+1),'CLASSIFIED ')
> FROM #TEMP
> The result is:
> Name: CLASSIFIED yyy DBX: xxx yyy Addr1: 1234
> Name: CLASSIFIED 123 DBX: aaa Addr1: 1234
> The corerct result would be:
> Name: CLASSIFIED DBX: xxx yyy Addr1: 1234
> Name: CLASSIFIED DBX: aaa Addr1: 1234
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Jerry, Thanks for all your help! I think I'll make it work.
*** Sent via Developersdex http://www.examnotes.net ***|||Try this out...
select
substring(Name,1,patindex('%DBX:%',Name)
-1)+
replace(substring(Name,patindex('%DBX:%'
,Name),len(Name)),
substring(Name,patindex('%DBX:%',Name)+5
,(charindex('Addr1:',Name,1)-patinde
x('%DBX:%',Name)-6)),
'APPLE')
as Output
from #temp
Regards
sudarshan
"Test Test" wrote:

> Hello!
> I am posting this problem again. I have gotten somebody's help with the
> right solution but I have one more case to work on. I would appreciate
> your time and help on this.
> create table #temp
> (ID int, Cust varchar(80))
> insert into #temp values(23, 'Name: abcd DBX: abcdefgh Addr1: 1234')
> insert into #temp values(27, 'Name: xyz DBX: xyz Addr1: 9999')
> insert into #temp values(25, 'Name: lmn DBX: opqr Addr1: 1234')
>
> -- Case 1: Put CENSORED between DBX: and Addr1: in the Cust column...IT
> WORKS!!!
> select Col1 = Left(Cust, charindex('DBX:', Cust) + 3) +
> replace(substring(Cust, charindex('DBX:', Cust) + 4, LEN(Cust)),
> substring(Cust, charindex('DBX:', Cust) + 4,
> charindex('Addr1:', Cust) - charindex('DBX:', Cust)-4), ' CENSORED ')
> from #temp
> -- Case 2: Put CLASSIFIED between Name: and DBX: in the Cust column..IT
> DOES NOT WORK for 'xyz' case bc 'xyz is in two places. I need it to
> replace only one time which is in bw Name: and DBX: and leave the other
> one untouched. how do I do that?
> select Col2 = replace(Cust, substring(Cust, charindex(':', Cust)+2,
> charindex('DBX:', Cust)- charindex(':', Cust)-2), ' CLASSIFIED ')
> from #temp
> My SQL for Col2 gives me this:
> Name: CLASSIFIED DBX: abcdefgh Addr1: 1234
> Name: CLASSIFIED DBX: CLASSIFIED Addr1: 9999
> Name: CLASSIFIED DBX: opqr Addr1: 1234
> And I want this..
> Name: CLASSIFIED DBX: abcdefgh Addr1: 1234
> Name: CLASSIFIED DBX: xyz Addr1: 9999
> Name: CLASSIFIED DBX: opqr Addr1: 1234
> Thanks for your help!
>
>
> *** Sent via Developersdex http://www.examnotes.net ***
>