PostgreSQL - get first characters or integers

To achieve the desired result, you can use a combination of PostgreSQL's string functions and a CASE statement to check the first characters of the description column and return the appropriate substring. Here's a query that should work for your requirements:

SELECT *,
  CASE
    WHEN SUBSTRING(description FROM 1 FOR 1) ~ '^[A-Za-z]' THEN SUBSTRING(description FROM 1 FOR 3)
    WHEN SUBSTRING(description FROM 1 FOR 1) ~ '^[0-9]' THEN SUBSTRING(description FROM 1 FOR 2)
    ELSE NULL
  END AS result
FROM codes
WHERE enable = -1
ORDER BY description ASC
LIMIT 100;

Explanation:

• SUBSTRING(description FROM 1 FOR 1) gets the first character of the description.
• SUBSTRING(description FROM 1 FOR 3) gets the first three characters of the description.
• SUBSTRING(description FROM 1 FOR 2) gets the first two characters of the description.
• The ~ operator is used to perform a regular expression match. '^[A-Za-z]' checks if the first character is a letter, and '^[0-9]' checks if the first character is a digit.
• The CASE statement checks the first character and returns the first three characters if it's a letter or the first two characters if it's a digit.
• The ELSE NULL part is optional and will return NULL if the first character is neither a letter nor a digit. You can adjust this to your needs.

This query will return all columns from the codes table, along with an additional column named result that contains either the first three letters or the first two digits of the description, depending on the content of the description column.