Post reply

replace in conjunction with substring ?

  • January 10, 2008 at 8:16 am

    #185269

    Hi

    I have a varchar(9) field that contains 9 digits e.g 123456789

    If I want to use an update statement to replace every single row of data with '666' for the first 3 digits can I do this using some form of replace with substring ?

    Ive tried this approach and cant get the syntax to work

    kind regards

    si

  • January 10, 2008 at 8:33 am

    #766687

    DECLARE @test-2 TABLE(

    STRING VARCHAR(9)

    )

    INSERT INTO @test-2

    SELECT '123456789' UNION ALL

    SELECT '123456669'

    UPDATE @test-2

    SET STRING = REPLACE(STRING,'666',LEFT(STRING,3))

    WHERE STRING LIKE '%666%'

    SELECT *

    FROM @test-2


    My blog: http://jahaines.blogspot.com

  • January 10, 2008 at 9:57 am

    #766723

    thank you 🙂

    in the end I used

    update table

    set number = replace (number,substring(number,1,4),6666)

    seemed to work ok

    kind regards

    si

  • January 10, 2008 at 10:24 am

    #766738

    I thought you wanted to replace the 666 with the first 3 digits??? You have your query backwards!! In your query you are replacing the first 4 digits of your number with 6666.

    The syntax for REPLACE is:

    Syntax

    REPLACE ( string_expression1 , string_expression2 , string_expression3 )

    Arguments

    string_expression1

    Is the string expression to be searched. string_expression1 can be of a character or binary data type.

    string_expression2

    Is the substring to be found. string_expression2 can be of a character or binary data type.

    string_expression3

    Is the replacement string. string_expression3 can be of a character or binary data type.


    My blog: http://jahaines.blogspot.com

  • January 10, 2008 at 10:26 am

    #766741

    You do not need to use substring if you are grabbing the first 4 chars.

    You query should look like this:

    update table

    set number = replace (number,6666,left(number,4))


    My blog: http://jahaines.blogspot.com

  • January 10, 2008 at 10:28 am

    #766743

    even better and easier !

    thanks si

Viewing 6 posts - 1 through 5 (of 5 total)

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