October 11, 2010 at 9:34 am
Hi,
I have created a table as below
CREATE TABLE [dbo].[EmpDetails](
[Name] [nvarchar](50) NULL,
[Address] [nvarchar](50) NULL,
[Town] [nvarchar](50) NULL,
[City] [nvarchar](50) NULL
) ON [PRIMARY]
I have inserted the value as below
INSERT INTO [Employee].[dbo].[EmpDetails] ([Name],[Address],[Town],[City])
VALUES ('abc','5th Street,ARY,ARYSHIRE','ARY','ARYSHIRE')
I want the address as
5th Street
So I gave the update query as below
update empdetails
set address= replace(address,town,'')
But what happened was the address got modified as below
5th Street,,SHIRE.
Now when I give
update empdetails
set address= replace(address,city,''),
now such a word called as ARYSHIRE does not exist.
How do I sort this problem.
This occurs in many parts of my table.
Kindly do let me know.
Regards
cmrhema
October 11, 2010 at 9:52 am
Parse the components of the address, using a standard string parsing routine. Then if the second component matches your town setting, replace just that component, then rebuild the string by using a standard concatenation technique.
For the string parsing, I recommend reading Phil and Robyn's article on Simple-Talk (www.simple-talk.com). Search for "Helper Table" on that page.
For concatenating, I recommend using the "for xml path" trick. You can Bing/Google that, and you'll find articles on how to do it. That works in SQL 2005 and 2008, which I'm assuming you're using because of the forum you posted in. If it's SQL 2000, you'll need a different solution.
But do note, this will only work if the addresses are formatted consistently like the example you gave. Are they?
If you need help on the specific steps, after looking those things up, I'm sure we can point you in the right direction on that too.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 11, 2010 at 9:55 am
Thanks for the reply
Yes, the field address will be exactly the same.
But sometimes it might change as below
5th Street,10th Main Road,ARY,ARYSHIRE.
Sorry forgot to mention, I will be executing the query in sql server 2000.
October 11, 2010 at 10:41 am
Since it's SQL 2000, the best bet will probably be a UDF that uses a Numbers or Tally table to split it apart into rows, based on the commas. That way, you can take out the part you want to if it's an exact match.
Would look something like this, but with your Numbers/Tally table, and columns instead of variables:
DECLARE @String VARCHAR(100), @Exclusion VARCHAR(100);
SELECT @String = 'number street, town, county', @Exclusion = 'town';
SELECT SUBSTRING(@String+',', number,
CHARINDEX(',', @String+',', number) - number)
FROM DBA.dbo.Numbers
WHERE number <= LEN(REPLACE(@String,' ','|'))
AND SUBSTRING(',' + @String,
number,
LEN(REPLACE(',',' ','|'))) = ','
AND LTRIM(SUBSTRING(@String+',', number,
CHARINDEX(',', @String+',', number) - number)) != @Exclusion;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply