SQL Server remove all spaces in data

Posted on 2007-07-19
Last Modified: 2011-08-18
I get "extracted date" that has a field that end users place their zipcodes in.

Unfortunately there are a lot of records with spaces in the zipcode field WITH the zipcode

'     99032'
' 99033       '

I need to strip ALL spaces.  Is there an update I can run?
Question by:lrbrister
    LVL 9

    Accepted Solution

    update table
    set field = rtrim(ltrim(field))
    LVL 15

    Assisted Solution

    Hi lrbrister,

    try something like this:
      zipcode = REPLACE(zipcode, ' ', '')
    LVL 15

    Expert Comment

    Hi lrbrister,

    Note my solution will remove spaces from the middle of zipcodes as well as either end - if you do not want this use gpompe's solution

    Author Comment

    Hey guys...both are great.  Splitting the points with a tiny bit more to gpompe:

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now