---

Return to the SQL Tips

---

Use SQL to Remove A selected Character From A Character Field

Hey, Ted:

Is it possible through SQL to remove a character from a text field?
I have a file that has a name field with the last name first and first name last. Some of the records were keyed with a comma separating the two names, as in SMITH, JOHN. Other records have no comma. I would like to remove the commas. The names are all different lengths so I can't use any kind of constant length.

Yes, SQL can handle this problem for you. First, select the records that have the comma, because those are the only ones you want to update. I doubt you have any records with a comma in the first position of the field, but just in case, exclude them by checking for a comma anywhere after the first position.

 
select * from customer                  
    where position (',' in custname) > 1
 

Use the position function again to extract the last and first names, which precede and follow the comma.

select custname, 
        substr(custname,1,position (',' in custname) - 1)
           concat 
        substr(custname,position(',' in custname) + 1) 
    from customer 
    where position (',' in custname) > 1 
 

Now that the select is working correctly, convert it to an update statement.

update customer 
   set custname = 
       substr(custname,1,position (',' in custname) - 1)
          concat
       substr(custname,position(',' in custname) + 1) 
   where position (',' in custname) > 1 
 

Be sure to try this on a copy of the production file first to verify that your SQL command will work correctly.

-- Ted Holt
From the September 28, 2001 issue of the OS/400 Edition of the Midrange Guru technical tips newsletter.
Volume 1, Number 3
A publication from Midrange Server, Inc.
http://www.itjungle.com

[report a broken link by clicking here]

---