SQL Server Performance

UPDATE value

Discussion in 'General DBA Questions' started by fauzanf, Feb 8, 2010.

  1. fauzanf New Member

    Dear My Friends,I have a question... If i use the following syntax...SELECT * FROM TableName WHERE LEFT(Field,3)The result is 'J10' for all records. It's more that 100 records.I want to update the value to J0. How is the syntax?Help me please...Thank you
    fauzanf, Feb 8, 2010
    #2

  2. FrankKalis Moderator

    This should work:
    UPDATE table
    SET COLUMN = REPLACE(COLUMN, 'J10', 'J0')
    WHERE
    column LIKE 'J10%'

    FrankKalis, Feb 8, 2010
    #3

  3. Adriaan New Member

    Note that if you have a value on the column like 'J10xyzJ10xyz', then Frank's solution will replace both occurrences of 'J10' , not just the first one.
    If you need to replace only the initial three characters, use something like
    UPDATE table
    SET column = 'J0' + SUBSTRING(column, 4, 100)
    WHERE column like 'J10%'
    Adjust the 100 figure to reflect the column length.
    Adriaan, Feb 9, 2010
    #4

  4. FrankKalis Moderator

    Oops, good catch. [:)]
    FrankKalis, Feb 9, 2010
    #5

  5. Madhivanan Moderator

    or
    UPDATE table
    SET column = 'J0' + STUFF(column, 1,3, 'J0')
    WHERE column like 'J10%'
    Madhivanan, Feb 10, 2010
    #6

Share This Page