I had the odd challenge of providing an account number by removing any values that were the same except for numbers at the end.
\nExamples custname01, custname02, custname03, etc.
\nSince these accounts are all really the same customer we need to summarize a report by custname, without the numbers.
\nHowever some accounts legitimately start with numbers and are truly different accounts. This came about because of a merger of two different systems.
\nExamples. 09876-a, 09876-b, etc<\/p>\n
Here is my solution:<\/strong> OUTPUT 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 … Continue reading
\nUse 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.<\/p>\n
\nSELECT account,
\nrollup_account = CASE
\n IF (PATINDEX('%[0-9]%', account) > 0)
\n THEN LEFT(account, (PATINDEX('%[0-9]%', account)-1)
\n ELSE account
\n END;
\nfrom tablename ...
\n<\/code><\/p>\n
\naccount | rollup_account
\ncustname01 | custname
\ncustname02 | custname
\ncustname03 | custname
\n09876-a | 09876-a
\n09876-b | 09876-b<\/p>\n","protected":false},"excerpt":{"rendered":"