I am new to SQL and attempting to create a query for PostgreSQL that deals with the following cases:
- I have a column with string data type.
- The strings in this column can be up to 11 digits long, but there are some records that only contain 6 or 7 digits. I only need to consider these records.
- In both cases, I need to find the last numeric digit and count the occurrences of this digit. For example, if I have the values 'BBB0123' and 'AA123A' on the column, the count of the digit '3' would be 2.
- Additionally, records with 7 digits have this structure: 'BBB0123' where the last numeric digit is at the end of the string. For records with 6 digits, the structure is like this: 'AA123A' where the last numeric digit is in the penultimate position of the string.
I would greatly appreciate any help you can provide.