I did something like this in a very large SQL medical records database. I had a numeric 'type' designator that determined the format. I did not do a table lookup in the formatting function as it took more time. I used a large CASE in the function to determine the format, and then either handled formatting directly or called another function to do a complicated format.
In my case, everything was returned as a string no matter the format because the string simply went to a report - no math or other functions required. It could just as easily have cast the string to another format if that was needed.
It was suprisingly fast.
>I have these fields in a table. These can be changed if someone has a better way to handle it. I am using values like 9N in the SSN field to mean that during processing, for this carrier, the result of the SSN should be numeric and have a length of 9. For the LastName, it can be alphanumeric and have a length of 35. The dates are just as obvious. For one like MemberID, it starts with a "W" and is followed by 9 numeric values.
>
>I'm currently heading down a path where I have a SQL statement that calls a function for each field. In the function I evaluate that field's formatting value that is in this table. Based on that, it runs through some CASE statements and the value is formatted and returned in the SQL result for that field.
>
>The issue is how to code to parse apart the format values below and then handle all the possibilities. Should I parse apart the values I have so that I know that 9N means a numeric value with a length of 9, and do the same with the other fields? That seems like a lot of detailed parsing, and I wonder if it is necessary. The dates are easy, but some of the others are not.
>
>Any ideas?
>
>
>
>FieldName Example of possible value
>
>PAYORID ZZCERT
>PAYORNAME O/P MOLINA HEALTHCARE MICHIGAN
>NPI 10N
>SSN 9N
>MEDICARE 10AN
>MEDICAID
>MEMBERID W+9N
>PROVIDERID 5A
>FEDTAXID 9N
>DOB CCYYMMDD
>LASTNAME 35AN
>FIRSTNAME 25AN
>SUFFIX 3AN
>TRANSDATE MMDDCCYY
>DOSBEGIN CCYYMMDD
>DOSEND CCYYMMDD
>GENDER 1A
>CARDNUMBER 15AN
>
____________________________________
Don't Tread on Me
Overthrow the federal government NOW!
____________________________________