title: Replace apostrophe SQL Server 2008 R2
body: <p>I have a table with about 300K records.  In order to import them exactly as they existed in the original file.  The original file had pipe and tilde delimiters.  I replaced the pipe tilde with a pipe apostrophe so that the fields come in as text.  Now I need to get rid of the apostrophe.  The database is actually storing the fields as ntext not text.  The real crunch here is that many of the fields are zero padded fields and I need them to remain zero padded within the database.</p>
  
  <p>Here is a sample of the text.  The first row is headers.</p>
  
  <blockquote>
    <p>Rec   'Article Number         'Substit.   'Group SKU              'Univ.
    Prod. Code        'Article Description                        'Cat 
      'Department Description                     'P.Cla      'Product CLass
    Description                   'SC     'Sub CLass Description<br>
      'FLY    'Family Description                         'Vendor article
    Number    'Pur    'Shi    'Gross weight           'UniWT      'Net weight<br>
      'Length                 'Width                  'Height<br>
      'UniDim     'Volume                 'UniVol     'Vendor Num     'Mfr Price<br>
      'Vendor Name                            'Mgr    'Category Mgr Name<br>
    00    '000000000022404373 '           '                   '43000670002<br>
      '67000 SBC House Blend T-Discs              '063    'Office Consumables<br>
      '12560  'Office Consumables                         '01 'Hot Beverage<br>
      '01     'COFFEE                                     '67000<br>
      'EA     'EA     '          14.700   'OZ     '          11.040   '<br>
    8.600     '           7.000   '           3.100   'IN     '           0.108   'FT3    '0000008228 '           'M BLOCK AND SONS INC<br>
      '017    'Patty Dunham       00  '000000000022404658 '           '<br>
      '400224046585       'NFR 2010 Merry Christmas 20Pk<br>
      '071    'Non-Merchandise Articles                   '30001  'OfficeMax<br>
      '11 'Merchandise                                '01     'Gift Card Program
      'SV1003846              'EA     'EA     '          16.000   'OZ     '<br>
    16.000    '           4.250   '           1.950   '           4.500   'IN     '           0.022   'FT3    '0000934003 '           'CERIDIAN STORED
    VALUE SOLUTIONS INC   'ZN1    'Non-Merchandise    00  '000000000022404676 '
      '                   '75353105641        "'Bubble Envelope 10.5""x15
    5Pk            "  '046    'Mailroom/Moving<br>
      '07303  'KRAFT WRAP/MAILERS                         '01 'MAILERS<br>
      '02     'BUBBLE                                     '756618<br>
      'EA     'EA     '          14.200   'OZ     '          14.200   '<br>
    16.500    '          11.250   '           1.250   'IN     '           0.134   'FT3    '0000008162 '           'SHURTECH BRANDS<br>
      '021    'Mailroom           00  '000000000022404756 '           '<br>
      '72512244814        'Champ .5mm 12pk Asstd Colors<br>
      '053    'Writing Instruments                        '10453  'Mechanical
    Pencils                       '02 'Refillable<br>
      '01     'Added Value (Refilla                       'AL15BP12M<br>
      'EA     'EA     '           5.000   'OZ     '           4.100   '<br>
    7.600     '           4.200   '           1.000   'IN     '           0.018   'FT3    '0000000146 '           'PENTEL OF AMERICA LTD.<br>
      '004    'Bryant Spencer</p>
  </blockquote>
  
  <p>I need to end up with text fields for all of the columns in the database, and the values as they appear in the sample above including leading zeros.</p>