SQL Server Performance

updating city name from another table's city name

Discussion in 'T-SQL Performance Tuning for Developers' started by pssheba, Oct 28, 2007.

  1. pssheba New Member

    Hi everyone,
    I have 2 tables: t1 and t2. Each table contains a "city" column. In t1 some city names have additional remark closed between parentheses eg
    (mycity (wonderful)) and i want city names in t2 to have those parentheses added. I do it as follows:
    [query]
    UPDATE b
    SET
    b.city=a.city
    FROM
    t1 AS a LEFT JOIN t2 AS b
    ON
    SUBSTRING(a.city,1,CHARINDEX('(',a.city)-1)=b.city
    WHERE
    a.city LIKE '%(%'
    [/query]
    Running the above code i got NULL to all city names in t2 !
    At the othe hand when i run:
    Code:
    SELECT
    a.city,
    b.city
    FROM
    t1 AS a LEFT JOIN t2 AS b
    ON
    SUBSTRING(a.city,1,CHARINDEX('(',a.city)-1)=b.city
    WHERE
    a.city LIKE '%(%'
    
    I get the "parenthesesed" cities side by side with the "non parenthesesed" cities.
    Can anyone solv that mystery ?
    Thanks
  2. Madhivanan Moderator

    Can you also post some sample data with expected output?

Share This Page