Post reply

remove white spaces

  • September 12, 2005 at 11:19 pm

    #165476

    i have some records which has a field which contains hyphen and spaces in between.

    example :

    34-123 45 67

    i want to remove those spaces and hyphen from the fields in those records.

    i.e records filed will be having

    341234567 // without space

    problem

    --------

    Problem is how do i find which records have the filed which conatains hyphen and spaces in between ? and how do i update that with removing hyphen and whitespaces ?

    can you please tell me the SQL query ?

    thank you

  • September 12, 2005 at 11:29 pm

    #589235

    update tablename set fieldname = replace(replace(fieldname, '-', ''), '-', '')

  • September 12, 2005 at 11:40 pm

    #589237

    1. i want to remove both the hyphen and whiite space

    2. but there are 10000 records which has has got this trouble in that field (Phone_Number). do you mean , i should run your SQL query 10000 times !!

  • September 12, 2005 at 11:49 pm

    #589238

    SELECT     REPLACE(REPLACE(COLUMN_NAME, ' ', ''), '-', '') ASCOLUMN_ALIAS

    FROM         TABLE_NAME


    subban

  • September 12, 2005 at 11:57 pm

    #589239

    SELECT REPLACE(REPLACE(COLUMN_NAME, ' ', ''), '-', '') ASCOLUMN_ALIAS

    FROM TABLE_NAME

    SELECT ! i dont want to display .

    ASCOLUMN_ALIAS ? what is this ?

    I AM CONFUSED.

    ALL i need is to remove the spaces and hyphen from my Phone_Number field of my Table.

    There are more than 10000 records. some records have this problem . i want to correct those.

    what i should do ?

  • September 13, 2005 at 12:24 am

    #589241

    UPDATE TABLE_NAME SET COLUMN_NAME=REPLACE(REPLACE(COLUMN_NAME, ' ', ''), '-', '')

     

    WHERE TABLE_NAME IS THE NAME OF THE TABLE

    COLUMN_NAME IS THE NAME OF THE CCOLUMN WITH IN THE TABLE WHICH U NEED TO CHANGE


    subban

  • September 13, 2005 at 1:41 am

    #589258

    "1. i want to remove both the hyphen and whiite space

    2. but there are 10000 records which has has got this trouble in that field (Phone_Number). do you mean , i should run your SQL query 10000 times !!"

    1. The query initially provided will achieve this. You'll notice it has the REPLACE function twice, once for the hyphen and once for the space.

    2. Running the query as-is will update every record in the table. If you don't want to update all the records you can add applicable criteria using the WHERE clause. Check Books Online for information on CHARINDEX, or PATINDEX, and use whichever suits your needs.

     

    --------------------
    Colt 45 - the original point and click interface

  • September 14, 2005 at 11:33 am

    #589835

    Just one additional point. You'll probably have to investigate the front-end application (or whereever the data originates) to make sure it is functioning properly as well. If the front-end is not fixed then you will be re-executing this query again (and again). What I'm trying to say is that you've got a fix for the database now you have to fix the root cause of the bad data problem too.

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

Viewing 8 posts - 1 through 7 (of 7 total)

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