Replace only the exact words

  • 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

  • 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

  • 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.

  • 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 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply