Fix Replace function plz!

Hello!
I need to replace a string which starts from "DBX:" and ends to "Addr1:"
with a word "APPLE". The cloumn is Name in #temp table. I am captuting
it correctly but not using the replace function the right way. It is
replacing eveywhere which I dont want.
Thanks for your help.
create table #temp
(ID int, Name varchar(80))
insert into #temp values(23, 'Name: Smith Black Jones DBX: Smith Jones
Addr1: 1234')
insert into #temp values(27, 'Name: John Doe DBX: John Doe Addr1: 9999')
insert into #temp values(25, 'Name: Batman DBX: Robin Addr1: 1234')
select
--ID,
--Captured = substring(Name, charindex('DBX:', Name) + 4,
--charindex('Addr1:', Name) - charindex('DBX:', Name)-4),
Final = replace(Name, substring(Name, charindex('DBX:', Name) + 4,
charindex('Addr1:', Name) - charindex('DBX:', Name)-4), ' APPLE ')
from #temp
I am getting this:
Name: Smith Black Jones DBX: APPLE Addr1: 1234
Name: APPLE DBX: APPLE Addr1: 9999
Name: Batman DBX: APPLE Addr1: 1234
And I want this:
Name: Smith Black Jones DBX: APPLE Addr1: 1234
Name: John Doe DBX: APPLE Addr1: 9999
Name: Batman DBX: APPLE Addr1: 1234
*** Sent via Developersdex http://www.examnotes.net ***Test,
Try:
SELECT SUBSTRING([NAME],7,DATALENGTH([NAME])) AS 'NAME'
FROM #TEMP
HTH
Jerry
"Test Test" <farooqhs_2000@.yahoo.com> wrote in message
news:OwySQyZ1FHA.3864@.TK2MSFTNGP12.phx.gbl...
> Hello!
> I need to replace a string which starts from "DBX:" and ends to "Addr1:"
> with a word "APPLE". The cloumn is Name in #temp table. I am captuting
> it correctly but not using the replace function the right way. It is
> replacing eveywhere which I dont want.
> Thanks for your help.
> create table #temp
> (ID int, Name varchar(80))
> insert into #temp values(23, 'Name: Smith Black Jones DBX: Smith Jones
> Addr1: 1234')
> insert into #temp values(27, 'Name: John Doe DBX: John Doe Addr1: 9999')
> insert into #temp values(25, 'Name: Batman DBX: Robin Addr1: 1234')
> select
> --ID,
> --Captured = substring(Name, charindex('DBX:', Name) + 4,
> --charindex('Addr1:', Name) - charindex('DBX:', Name)-4),
> Final = replace(Name, substring(Name, charindex('DBX:', Name) + 4,
> charindex('Addr1:', Name) - charindex('DBX:', Name)-4), ' APPLE ')
> from #temp
> I am getting this:
> Name: Smith Black Jones DBX: APPLE Addr1: 1234
> Name: APPLE DBX: APPLE Addr1: 9999
> Name: Batman DBX: APPLE Addr1: 1234
> And I want this:
> Name: Smith Black Jones DBX: APPLE Addr1: 1234
> Name: John Doe DBX: APPLE Addr1: 9999
> Name: Batman DBX: APPLE Addr1: 1234
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Nothing wrong with the replace function, basically what you asked for was
this:
REPLACE('Name: John Doe DBX: John Doe Addr1: 9999', 'John Doe', 'APPLE')
This is what you're looking for I think:
select
--ID,
--Captured = substring(Name, charindex('DBX:', Name) + 4,
--charindex('Addr1:', Name) - charindex('DBX:', Name)-4),
--Final = replace(Name, substring(Name, charindex('DBX:', Name) + 4,
--charindex('Addr1:', Name) - charindex('DBX:', Name)-4), ' APPLE '),
Correct = Left(Name, charindex('DBX:', Name) + 3) +
replace(substring(Name, charindex('DBX:', Name) + 4, LEN(Name)),
substring(Name, charindex('DBX:', Name) + 4,
charindex('Addr1:', Name) - charindex('DBX:', Name)-4), ' APPLE ')
from #temp
By the way, wouldn't it be much a bit cleaner to have separate columns for
Name, DBX and Addr1?
"Test Test" <farooqhs_2000@.yahoo.com> wrote in message
news:OwySQyZ1FHA.3864@.TK2MSFTNGP12.phx.gbl...
> Hello!
> I need to replace a string which starts from "DBX:" and ends to "Addr1:"
> with a word "APPLE". The cloumn is Name in #temp table. I am captuting
> it correctly but not using the replace function the right way. It is
> replacing eveywhere which I dont want.
> Thanks for your help.
> create table #temp
> (ID int, Name varchar(80))
> insert into #temp values(23, 'Name: Smith Black Jones DBX: Smith Jones
> Addr1: 1234')
> insert into #temp values(27, 'Name: John Doe DBX: John Doe Addr1: 9999')
> insert into #temp values(25, 'Name: Batman DBX: Robin Addr1: 1234')
> select
> --ID,
> --Captured = substring(Name, charindex('DBX:', Name) + 4,
> --charindex('Addr1:', Name) - charindex('DBX:', Name)-4),
> Final = replace(Name, substring(Name, charindex('DBX:', Name) + 4,
> charindex('Addr1:', Name) - charindex('DBX:', Name)-4), ' APPLE ')
> from #temp
> I am getting this:
> Name: Smith Black Jones DBX: APPLE Addr1: 1234
> Name: APPLE DBX: APPLE Addr1: 9999
> Name: Batman DBX: APPLE Addr1: 1234
> And I want this:
> Name: Smith Black Jones DBX: APPLE Addr1: 1234
> Name: John Doe DBX: APPLE Addr1: 9999
> Name: Batman DBX: APPLE Addr1: 1234
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Thanks but this is not what I am looking for. I need this:
Name: Smith Black Jones DBX: APPLE Addr1: 1234
Name: John Doe DBX: APPLE Addr1: 9999
Name: Batman DBX: APPLE Addr1: 1234
The APPLE has been updated in between the DBX: and Addr1: in the Name
column. My SQL is not working in John Doe case but John Doe is in two
places and I want to see the replacement in one place only (which is
from DBX: to Addr1:).
Hope this information helps.
*** Sent via Developersdex http://www.examnotes.net ***|||I don't know what to tell you, but my query returned me this (which is what
you say you're looking for) :
Name: Smith Black Jones DBX: APPLE Addr1: 1234
Name: John Doe DBX: APPLE Addr1: 9999
Name: Batman DBX: APPLE Addr1: 1234
Copy and paste this (just to verify that there wasn't a copy / paste mistake
when you ran it last time) :
___
create table #temp
(ID int, Name varchar(80))
insert into #temp values(23, 'Name: Smith Black Jones DBX: Smith Jones
Addr1: 1234')
insert into #temp values(27, 'Name: John Doe DBX: John Doe Addr1: 9999')
insert into #temp values(25, 'Name: Batman DBX: Robin Addr1: 1234')
select
--ID,
--Captured = substring(Name, charindex('DBX:', Name) + 4,
--charindex('Addr1:', Name) - charindex('DBX:', Name)-4),
--Final = replace(Name, substring(Name, charindex('DBX:', Name) + 4,
--charindex('Addr1:', Name) - charindex('DBX:', Name)-4), ' APPLE '),
Correct = Left(Name, charindex('DBX:', Name) + 3) +
replace(substring(Name, charindex('DBX:', Name) + 4, LEN(Name)),
substring(Name, charindex('DBX:', Name) + 4,
charindex('Addr1:', Name) - charindex('DBX:', Name)-4), ' APPLE ')
from #temp
___
"Test Test" <farooqhs_2000@.yahoo.com> wrote in message
news:%23YMaCDa1FHA.3816@.TK2MSFTNGP14.phx.gbl...
> Thanks but this is not what I am looking for. I need this:
> Name: Smith Black Jones DBX: APPLE Addr1: 1234
> Name: John Doe DBX: APPLE Addr1: 9999
> Name: Batman DBX: APPLE Addr1: 1234
> The APPLE has been updated in between the DBX: and Addr1: in the Name
> column. My SQL is not working in John Doe case but John Doe is in two
> places and I want to see the replacement in one place only (which is
> from DBX: to Addr1:).
> Hope this information helps.
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Sorry test...misread the data requirements prior to posting this query.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:u6EPH3Z1FHA.2792@.tk2msftngp13.phx.gbl...
> Test,
> Try:
> SELECT SUBSTRING([NAME],7,DATALENGTH([NAME])) AS 'NAME'
> FROM #TEMP
>
> HTH
> Jerry
> "Test Test" <farooqhs_2000@.yahoo.com> wrote in message
> news:OwySQyZ1FHA.3864@.TK2MSFTNGP12.phx.gbl...
>|||Thanks ESPNSTI! It works fine! Sorry I missed the solution in your
initial posting. Thanks!
*** Sent via Developersdex http://www.examnotes.net ***|||ESPN! I have one more condiction to take care of which is to replace
anything after "Name:" and before "DBX:" with a word ' XXXX '.
so I tried it but again my SQL is not working for John Doe bc it is in
two place.
Now,
select replace(Name, substring(Name, charindex(':', Name)+2,
charindex('DBX:', Name)- charindex(':', Name)-2), ' XXXXX ')
from #temp
I am getting this:
Name: XXXXX DBX: Smith Jones Addr1: 1234
Name: XXXXX DBX: XXXXX Addr1: 9999
Name: XXXXX DBX: Robin Addr1: 1234
An I want this:
Name: XXXXX DBX: Smith Jones Addr1: 1234
Name: XXXXX DBX: John Doe Addr1: 9999
Name: XXXXX DBX: Robin Addr1: 1234
I would appreciate your help! Thanks.
*** Sent via Developersdex http://www.examnotes.net ***