home *** CD-ROM | disk | FTP | other *** search
/ NetNews Usenet Archive 1992 #18 / NN_1992_18.iso / spool / comp / database / ingres / 1128 < prev    next >
Encoding:
Text File  |  1992-08-12  |  1.5 KB  |  48 lines
  1. Newsgroups: comp.databases.ingres
  2. Path: sparky!uunet!cs.utexas.edu!sdd.hp.com!mips!darwin.sura.net!uvaarpa!cv3.cv.nrao.edu!mail-to-news-gateway
  3. From: CS_PAUL@GSBVAX.UCHICAGO.EDU (Paul Ford 312/702-0335)
  4. Subject: RE: String Substitution
  5. Message-ID: <920812122007.20a00063@GSBVAX.UCHICAGO.EDU>
  6. Sender: root@nrao.edu (Operator)
  7. Organization: National Radio Astronomy Observatory
  8. Date: Wed, 12 Aug 1992 17:20:07 GMT
  9. Lines: 37
  10.  
  11. Michael McGarrigle writes:
  12.  
  13. >We wish to replace all occurences of a "-" with a space in
  14. >the employee_name field in a table.
  15.  
  16. It's ugly, but you can use the string functions left, right, locate, and
  17. length to do it.
  18.  
  19.     update employee
  20.     set
  21.     employee_name =
  22.        left( employee_name, locate(employee_name,'-')-1)
  23.      + ' '
  24.      + right( employee_name, length(employee_name)-locate(employee_name,'-'))
  25.     where
  26.     employee_name like '%-%'
  27.  
  28. This only takes care of the first '-'.  The update has to be repeated
  29. until it affects 0 rows.
  30.  
  31. Try a select first to convince yourself that it works:
  32.  
  33.     select fixed_name =
  34.        left( employee_name, locate(employee_name,'-')-1)
  35.      + ' '
  36.      + right( employee_name, length(employee_name)-locate(employee_name,'-'))
  37.     from employee
  38.     where
  39.     employee_name like '%-%'
  40.  
  41. Paul Ford
  42. --------------------------------------------------------------------------
  43. GSB Computing Services                  312.702.0335
  44. University of Chicago                   cs_paul@gsbvax.uchicago.edu
  45. 1101 E. 58th Street
  46. Chicago IL 60637
  47. --------------------------------------------------------------------------
  48.