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.
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