1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: SQL Syntax Question

Discussion in 'Software Development' started by Carlos_Mendoza, Dec 25, 2012.

Thread Status:
Not open for further replies.
Advertisement
  1. Carlos_Mendoza

    Carlos_Mendoza Thread Starter

    Joined:
    Nov 12, 2011
    Messages:
    124
    A few records in a database of mine have become corrupted. I was wondering if someone could write an update query for me (for SQL Server) which would put the records back into the proper formatting. The incorrect formatting is Mendoza ,Carlos. The proper formatting should read Mendoza, Carlos. Is this possible to do through a SQL Update Query? If so can someone give me the syntax that will accomplish this?

    Thanks in advance and Merry Christmas!

    EDIT ---
    I tried this syntax
    Code:
    update mytable 
    set mycolumn = replace(mycolumn, ' ,', ',  '
    
    and that corrected one issue. My next question is, what needs to be added to this statement to drop the comma IF the last text in the field is a comma? That way I only have to run one update statement?
     
  2. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    Just add a "WHERE" clause using the RIGHT function. Just replace the comma with an empty string.

    NOTE: I have not tested this but in theory it should work so make sure to back up your data or test on a small test subset of data. Also keep in mind that SQL Server does not have a "TRIM" function so this may or may not work depending on whether there is a blank space after the comma. If all else fails you should be able to export the entire table to CSV or other external file format and then make your changes in Excel and then re-import.

    Rollin
     
  3. Carlos_Mendoza

    Carlos_Mendoza Thread Starter

    Joined:
    Nov 12, 2011
    Messages:
    124
    When I run that syntax it tells me 0 rows affected even tho there are records where the right most character is a ,
    I also tried where RIGHT is ', ' to see if possibly the right most character was a space.
     
  4. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    If you are going to use the RIGHT function with logic to search for a space you need to search for 2 characters instead of one


    Rollin
     
  5. Carlos_Mendoza

    Carlos_Mendoza Thread Starter

    Joined:
    Nov 12, 2011
    Messages:
    124
    That got it, thank you!
     
  6. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1082371

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice