|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Fix Replace function plz!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.developersdex.com *** Test,
Try: SELECT SUBSTRING([NAME],7,DATALENGTH([NAME])) AS 'NAME' FROM #TEMP HTH Jerry Show quote "Test Test" <farooqhs_2***@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.developersdex.com *** Sorry test...misread the data requirements prior to posting this query.
Show quote "Jerry Spivey" <jspi***@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_2***@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.developersdex.com *** > > 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? Show quote "Test Test" <farooqhs_2***@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.developersdex.com *** 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.developersdex.com *** 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 ___ Show quote "Test Test" <farooqhs_2***@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.developersdex.com *** Thanks ESPNSTI! It works fine! Sorry I missed the solution in your
initial posting. Thanks! *** Sent via Developersdex http://www.developersdex.com *** 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.developersdex.com ***  |
|||||||||||||||||||||||
Other interesting topics