How to format data that was Input Masked

RLJB

We collect a lot of US cell phone numbers and use "Input Mask" to ensure we get the correct format so we can send SMS to them:

When we use this number to generate a letter or email we want it to appear like the masked number. But it doesn't - it appears like:

1234567899

This is a problem because it is ugly. We want to be able to format this back. But with some restrictions:

We don't want to have another attribute for each Cell Phone just to store a different format with BO rules etc (seems really inefficient to do this)
We don't want to save the masking symbols in the database because we send SMS to it and it ruins the send if there are spaces and () in it. So we don't want to change this:

I guess we'd like a FUNCTION that displays the mask, but trying to use AS_STRING and AS_NUMBER didn't work, for example this doesn't work:

AS_STRING(Customer.Phone,'# (###) ###-####')

Any ideas how?

BobK

Let me make sure I understand your requirements.

You save the cell phone number in the database as: 12345678901.
When sending letters or emails you what the cell phone number to display as: 1 (234) 567-8907
So you are looking for a function to convert 12345678901 to 1 (234) 567-8907

You could do this using several SUBSTRING functions to pull out the various pieces of the cell phone number and add the mask fields as needed.
This could get ugly.

A better approach (IMHO) would be to store the data in the database with the mask characters.
Then when sending letters or emails you do not need to change anything.

When sending SMS you could use the REPLACE_PATTERN function to remove any non-numeric characters
REPLACE_PATTERN(BO.CellNumber, '[⁰-9]', '')

RLJB

Nice idea Bob... will give '[⁰-9]' a go... thanks!