Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2010
    Posts
    87

    Unanswered: Update Query Find and Replace instead of Regular Find and Replace

    Quick question.

    If I want to find the character ; each time it shows up in a field and replace it with , . What would I do?

    Table name is "ONE BIG TABLE", field name is "LG_DESC".

    I guess I'm looking for code I can use in SQL View for an Update Query!

    TY

  2. #2
    Join Date
    Aug 2010
    Posts
    87
    I tried this


    UPDATE [ONE BIG TABLE] SET [ONE BIG TABLE].KEYWRDS = ","
    WHERE ((([ONE BIG TABLE].KEYWRDS)=";"));


    but it gives me back the ole "Will update 0 records". I need to replace the ; if it's within the data, so maybe it's looking for an exact match and because it's within the data it's not finding it?

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    that will not do what you think
    if that ran it would set the value of [ONE BIG TABLE].KEYWRDS to "," if [ONE BIG TABLE].KEYWRDS was ';'
    so it would work if the column keywrds only contained the symbol ";"

    you could use the wild card symbols to tell the SQL engine to look for ";" anywhere in the column keywrds, but your next problem is that you are trying to set the new value as ","
    ie
    if the current value of Keywords was "... and we use a semi colon; here; and here again; balh di blah"
    your current code would set Keywrds to ",".

    you'd be better off using the repalce function.. there is a reason why I keep suggesting you read up on the Access string functions

    so you'd be better off
    using something like
    update mytable
    set mycolumn = replace(mycolumn, ";", ",")
    where mycolumn like "*;*";[code]

    as ever before running an update query take a backup so if the update or delete goes wrong you haven't affected your data
    you can test the where clause by usign a query such as
    [code]select * from mytable
    where mycolumn like "*;*";[code]

    however Im confused:-
    you intially refer to LG_DESC as the column name with the problem, then refer to a different column KEYWRDS
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Aug 2010
    Posts
    87
    "Access string functions"

    Believe me, I'm reading. My challenge is that everywhere I go, there's a seemingly different spin on string functions. I guess I need a book or a good tutorial somewhere. I'm likely to read the web or tutorial.

  5. #5
    Join Date
    Aug 2010
    Posts
    87
    "however Im confused:- you intially refer to LG_DESC as the column name with the problem, then refer to a different column KEYWRDS"

    You are rightly confused. I was looking at my 5 remaining tasks and wrote the wrong thing / confused it with another post.

  6. #6
    Join Date
    Aug 2010
    Posts
    87
    To bring closure to this thread. Your instructions once again worked!

    This is what worked. And yes I did backup my data Now I have a question though. See below.


    UPDATE [ONE BIG TABLE] SET [ONE BIG TABLE].KEYWRDS = Replace(KEYWRDS,";",",")
    WHERE ((([ONE BIG TABLE].KEYWRDS) Like "*;*"));

    Related / New Question(S)
    If I want to take this same data set and say, replace X with Y would this be correct? This is to save for future use.

    UPDATE [ONE BIG TABLE] SET [ONE BIG TABLE].KEYWRDS = Replace(KEYWRDS,"X"Y"Y")
    WHERE ((([ONE BIG TABLE].KEYWRDS) Like "*X*"));

    If I wanted to just strip the character ™ and replace it with nothing, would this be correct?
    UPDATE [ONE BIG TABLE] SET [ONE BIG TABLE].KEYWRDS = Replace(KEYWRDS,"™""")
    WHERE ((([ONE BIG TABLE].KEYWRDS) Like "*™*"));

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by RBusiness View Post
    If I wanted to just strip the character and replace it with nothing, would this be correct?
    UPDATE [ONE BIG TABLE] SET [ONE BIG TABLE].KEYWRDS = Replace(KEYWRDS,"""")
    WHERE ((([ONE BIG TABLE].KEYWRDS) Like "**"));
    right idea, wrong implementation

    Code:
    UPDATE [ONE BIG TABLE] SET [ONE BIG TABLE].KEYWRDS = Replace(KEYWRDS,"X","Y")
    WHERE ((([ONE BIG TABLE].KEYWRDS) Like "*X*"));
    to be honest I don't know what would happen using replace on . I don't know if is stored as a string or special character. but looking at Google it seems Access uses Chr$(174) for the tm mark
    Trademark Symbol - Microsoft Access / VBA

    Code:
    UPDATE [ONE BIG TABLE] SET [ONE BIG TABLE].KEYWRDS = Replace(KEYWRDS,"","")
    WHERE ((([ONE BIG TABLE].KEYWRDS) Like "**"));
    if that fails then try
    Code:
    UPDATE [ONE BIG TABLE] SET [ONE BIG TABLE].KEYWRDS = Replace(KEYWRDS, Chr$(174), "")
    WHERE ((([ONE BIG TABLE].KEYWRDS) Like "**"));
    the chr function returns the symbol represented by the number form the current ascii character table. there is a corresponding function which returns the ascii value of the symbol ASC(mysymbol)
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by RBusiness View Post
    "Access string functions"

    Believe me, I'm reading. My challenge is that everywhere I go, there's a seemingly different spin on string functions. I guess I need a book or a good tutorial somewhere. I'm likely to read the web or tutorial.
    well for a start there's google
    Google

    which has a very promising reference (N0:2 on the list returned to me) of:-
    String Functions Listed By Name
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Aug 2010
    Posts
    87
    Thank you

    On the replace. this did work.

    UPDATE [ONE BIG TABLE] SET [ONE BIG TABLE].KEYWRDS = Replace(KEYWRDS,"","")
    WHERE ((([ONE BIG TABLE].KEYWRDS) Like "**"));

    The generic find and replace was just to save as a base for future ones.

  10. #10
    Join Date
    Aug 2010
    Posts
    87
    "which has a very promising reference (N0:2 on the list returned to me) of:-
    String Functions Listed By Name"

    That's good info! I'm checking it out now! Thank you very much!

Posting Permissions

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