Results 1 to 14 of 14
  1. #1
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Unanswered: Find & Replace qry

    TblName.[Office Name]="Century 21 Hilltop Realty"

    What I need is an update query which will change Century 21 to C-21 leaving Hilltop Realty untouched.

    Thanks . . . Rick
    Last edited by Rick Schreiber; 04-25-14 at 22:36.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Try:
    Code:
    UPDATE TblName SET TblName.[Office Name] = Replace(TblName.[Office Name], "Century 21", "C-21")
    Have a nice day!

  3. #3
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Look Good but

    This queried all records in the table?

    Look good but ? ? ?

    Rick

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    So put a where clause on to limit the rows processed
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Mar 2004
    Location
    California
    Posts
    502
    Where would the WHERE clause go-Replace?

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    There is no real need to restrict the data set with a WHERE clause, except perhaps for performance reasons (although this remains to be demonstrated). If you really want to, you can use:
    Code:
    WHERE TblName.[Office Name] LIKE "*Century 21*"
    Have a nice day!

  7. #7
    Join Date
    Mar 2004
    Location
    California
    Posts
    502
    This executes but no records show . . .

    UPDATE SandicorRoster SET SandicorRoster.[Office Name] = Replace(SandicorRoster.[Office Name],"*Century 21*","C-21")
    WHERE (SandicorRoster.[Office Name] ="*Century 21*" );

    Rick

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I used the LIKE operator in my criteria, not the = operator.
    Have a nice day!

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you have already run the query once, and it processed the rows, then there will be no point in runnign another query as the work has been done.

    Sinndho is right, you don't need a where clause, as the actual update bit will only work if the replace finds the specified value.

    however Im surprised that after 10 years you don't know where a WHERE clause goes.
    https://www.google.co.uk/search?q=ms...m=122&ie=UTF-8
    or perhaps I should be more scared that you dont' know how to use a search engine such as google to answer such questions
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The only reason for using a criteria (WHERE clause) is indeed for performance, at least with Access. I tested with a table of 25 000 rows and here are the results:
    Access 2003 SP2
    - Without criteria: 169 milliseconds.
    - With criteria: 78 milliseconds.

    SQL Server 2008 R2
    - Without criteria: 64 milliseconds.
    - With criteria: 58 milliseconds.

    And now, what hurts the most:
    Access 2010 SP1
    - Without criteria: 302 milliseconds.
    - With criteria: 148 milliseconds.

    Note:All db engines running on the same machine and the same O.S..
    Have a nice day!

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by Sinndho View Post
    The only reason for using a criteria (WHERE clause) is indeed for performance
    ...seems a good enough reason for me

    using a where clause seems a smart call in virtually all circumstances. and that performance gain will improves especially on large datasets and if that column is indexed
    I'd rather be riding on the Tiger 800 or the Norton

  12. #12
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Success . . .

    This is what works perfectly.

    Code:
    UPDATE SandicorRoster SET SandicorRoster.[Office Name] = Replace(SandicorRoster.[Office Name],"Century 21","C-21")
    WHERE (((SandicorRoster.[Office Name]) Like "*Century 21*"));
    It changes Century 21 to C-21 and leaves the rest.

    Yes, it's been ten years . . . or longer!

    I'm 65 now and much of what I learned was via the great (and patient) people here taught me!

    I'm a little slower now and things take longer for me to process.

    You know the old saying if you don't use it you loose it!

    I don't remember much of what I learned as I still use what you ll helped me with. No need to recreate it again.

    Thanks so much.

    Rick

  13. #13
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

  14. #14
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Syrictly speaking if you want to replace the text Century 21 which is at the START of a column in your db then dont use the leading wild card operator.

    ....like "Century 21*"

    It doesnt affect the function of your query, but it may make the query less intensive and use less processor resource
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •