1 Reply Latest reply: May 29, 2013 8:27 AM by Michael Armstrong-Smith RSS

    Newbie Assist

    1011626
      Hello, please excuse my noob type questions but I am not a SQL programmer, just a basic user of Discoverer.

      I have a field 'SSAN', formatted 123-45-6789, I need to replace it with 456789. I have tried using some LTRIM scripts I found online but I have been unable to make it work.

      Thanks in advance.

      James
        • 1. Re: Newbie Assist
          Michael Armstrong-Smith
          Hi James
          If your format is always the same, you need to do a two-step process.

          Step 1: Remove the dashes
          Step 2: Pull out the last 6 places

          You can remove the dashes using the REPLACE command, and you can strip out a series of characters using the SUBSTR command, and you can put them into a single command if need be.

          Step 1: Remove the dashes like this: REPLACE(SSAN, '-', NULL) - this replaces all dashes with NULL so that 123-45-6789 becomes 123456789

          Step 2: Pull out the last 6 places like this: SUBSTR(NEWSSAN, 4, 6) - this pulls 6 characters starting in position 4

          Putting this together avoids the need for an intermediate placeholder like this: SUBSTR(REPLACE((SSAN, '-', NULL), 4, 6)

          Hope this helps
          Michael