Could someone please help me with the following?
I need to generate a string based on a mask provided. The source string is a bank account number consisting of 26 digits. I then need to create a string based on all the masks that apply to the given bank account number.
Let's assume my bank account number is: 38116022445159832409307409
In my Masks table, I have the following 6 masks (the number of masks may vary) which apply to this bank account number (this is determined based on characters 3 through 10 of the bank account number matching characters 3 through 10 in the mask):
XX11602244YYYXXXXXXXXXXXXX
XX11602244YYYYXXXXXXXXXXXX
XX11602244YYYYXXXXXYYXXXXX
XX11602244YYYYYXXXXXXXXXXX
XX11602244YYYYYYXXXXXXXXXX
XX11602244YYYYYYYXXXXXXXXX
XX11602244YYYYYYYXXXXXXXYY
The output strings should be created by replacing all the Y's with the corresponding digits from the bank account number. Therefore, the output strings for the above bank account number and masks should be as follows:
mask -> output string
XX11602244YYYXXXXXXXXXXXXX -> XX11602244515XXXXXXXXXXXXX
XX11602244YYYYXXXXXXXXXXXX -> XX116022445159XXXXXXXXXXXX
XX11602244YYYYXXXXXYYXXXXX -> XX11602244515XXXXXX93XXXXX
XX11602244YYYYYXXXXXXXXXXX -> XX1160224451598XXXXXXXXXXX
XX11602244YYYYYYXXXXXXXXXX -> XX11602244515983XXXXXXXXXX
XX11602244YYYYYYYXXXXXXXXX -> XX116022445159832XXXXXXXXX
XX11602244YYYYYYYXXXXXXXYY -> XX116022445159832XXXXXXX09
I though about using PATINDEX but didn't really got anywhere with it.
I wanted to point out that Y (representing a digit from the pre-masked bank account number) may occur in any position in the mask (other than characters 3 to 10) and can be of any length (the count of X and Y cannot be higher than 18).
Solutions provided here Apply a Mask to Format a String in SQL Server Query/View do not seem to strictly correspond to this issue.