REPLACE query help plz!

21 Oct 2005 4:38 PM

Test Test

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.developersdex.com ***

21 Oct 2005 4:55 PM

Jerry Spivey

Test,

Try:

SELECT STUFF(CUST,1,CHARINDEX(' ',CUST,CHARINDEX(' ',CUST)+1),'CLASSIFIED ')
FROM #TEMP

HTH

Jerry
Show quote

"Test Test" <farooqhs_2***@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.developersdex.com ***

21 Oct 2005 6:05 PM

Test Test

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.developersdex.com ***

21 Oct 2005 6:16 PM

Jerry Spivey

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
Show quote

"Test Test" <farooqhs_2***@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.developersdex.com ***

21 Oct 2005 7:43 PM

Test Test

Jerry, Thanks for all your help! I think I'll make it work.

*** Sent via Developersdex http://www.developersdex.com ***

24 Oct 2005 10:58 AM

sudarshan selvaraja

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)-patindex('%DBX:%',Name)-6)),
        'APPLE')
    as Output
from #temp

Regards
sudarshan

Show quote

"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.developersdex.com ***
>