I had the odd challenge of providing an account number by removing any values that were the same except for numbers at the end.
Examples custname01, custname02, custname03, etc.
Since these accounts are all really the same customer we need to summarize a report by custname, without the numbers.
However some accounts legitimately start with numbers and are truly different accounts. This came about because of a merger of two different systems.
Examples. 09876-a, 09876-b, etc
Here is my solution:
Use CASE and PATINDEX to separate the accounts into their distinct appropriate groups and create a filed called rollup_account that is the summary point. PATINDEX will show the first occurrence of a string defined and return its position value. So if a number is found after the first position I want to return the alpha string before the numbers. If it starts with a number just report the original account value; PATINDEX will return zero if a number is found as the first character in the string.
SELECT account,
rollup_account = CASE
IF (PATINDEX('%[0-9]%', account) > 0)
THEN LEFT(account, (PATINDEX('%[0-9]%', account)-1)
ELSE account
END;
from tablename ...
OUTPUT
account | rollup_account
custname01 | custname
custname02 | custname
custname03 | custname
09876-a | 09876-a
09876-b | 09876-b
