PostgreSQL query for getting last numeric digit in a string

Transact-SQL
1

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.

2

Since this is a SQL Server forum - not sure you will get many answers specific to PostgreSQL. With that said - to get only those rows where the column is 6 or 7 characters:

WHERE LEN(column) IN (6, 7)  -- Might be LENGTH in PostgreSQL, not sure

To get the last numeric value - based on your defined formats:

CASE WHEN LEN(column) = 6 THEN SUBSTRING(column, 5, 1) ELSE NULL END
CASE WHEN LEN(column) = 7 THEN SUBSTRING(column, 7, 1) ELSE NULL END

Or - you can combine it into this:

SUBSTRING(column, CASE WHEN LEN(column) = 6 THEN 5 ELSE 7 END, 1)

To count - you would then need to group by the value:

SELECT numeric_value = SUBSTRING(column, CASE WHEN LEN(column) = 6 THEN 5 ELSE 7 END, 1)
     , count_numeric_value = count(*)
  FROM yourTable
 WHERE LEN(column) IN (6, 7)
 GROUP BY
       SUBSTRING(column, CASE WHEN LEN(column) = 6 THEN 5 ELSE 7 END, 1);

This is written using T-SQL - so you will need to adjust for specific PostgreSQL. If I recall correctly - the SUBSTRING function would be written as substring(column from 6 for 1) and LEN would be char_length(column) or length(column).

I think you could also do something like this:

CASE WHEN length(column) = 6 THEN right(left(column, -1), 1) ELSE right(column, 1) END

Or maybe something like this:

right(CASE WHEN length(column) = 6 THEN left(column, -1) ELSE column END, 1)
1 Like
3

You can use SUBSTRING('string' FROM 'regex pattern')