locked
Removing Commas and Spaces from Field RRS feed

  • Question

  • I have a table called TableD1 in a database with a field called "Range".

    The ranges are like 1-1000; 1001-2000; etc. up to like 1000000-200000.

    That is how they are supposed to look, they are not numeric fields, just general, containing a low, a dash and a high all in the same field.

    I just discovered that quite a few of the entries have included commas and spaces, like 1-1,000 or 100000 - 200000.

    I need to write a Query that will remove all of the commas from the data in TableD1's "Range" field, then all of the spaces.

    I imagine they are some kind of Update statements but because of the potential to blow up the whole thing, I'm reluctant to experiment.

    Does anyone know what code I should use?

    Thank you in advance for any help.


    Gee

    Tuesday, July 22, 2014 4:31 PM

Answers

  • you can use REPLACE for this

    UPDATE TableName
    SET Range = REPLACE(REPLACE(Range,' ',''),',','')
    WHERE Range LIKE '% %'
    OR Range LIKE '%,%'


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Marked as answer by GretaF Tuesday, July 22, 2014 5:53 PM
    Tuesday, July 22, 2014 4:45 PM

All replies

  • Making sure you've taken a backup. Pull the data into temp table and do the testing. once you satisfied with the output then do the update on the main table.

    create table #temp
    (
    Range varchar(100)
    )
    insert into #temp values('1-1,000'),('100000 - 200000')
    
    select replace(replace(Range,',',''),' ','') from #temp
    
    --update #temp set Range=replace(replace(Range,',',''),' ','') 

    Validate the the output of the select statement.

    --Prashanth

    Tuesday, July 22, 2014 4:44 PM
  • you can use REPLACE for this

    UPDATE TableName
    SET Range = REPLACE(REPLACE(Range,' ',''),',','')
    WHERE Range LIKE '% %'
    OR Range LIKE '%,%'


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Marked as answer by GretaF Tuesday, July 22, 2014 5:53 PM
    Tuesday, July 22, 2014 4:45 PM
  • YES!!

    That worked a treat Visakh16!

    Thank you .


    Gee

    Tuesday, July 22, 2014 5:53 PM