Postgresql: Concatenating regexp_split_to_table into a single string

Question

In Postgresql, I need to take an ASCII string like CNR39XL and turn it into a number like 21317392311 (ASCII values - 65, but digits left as is). This is to take some logic in our software and push it down into the databse level. I’m partway there with this:

ourdb=> SELECT CASE
ourdb->          WHEN ASCII(c) < 65 THEN c::integer
ourdb->          ELSE                    ASCII(c)-65
ourdb->        END
ourdb->   FROM regexp_split_to_table('CNR39XL','') s(c);
 case
------
    2
   13
   17
    3
    9
   23
   11
(7 rows)

However, I can't figure out how to take that final set of numbers and convert it into the string. What am I looking for?

Laurenz Albe · Accepted Answer · 2020-11-10 16:44:48Z

1

Use string_agg

SELECT CAST(
          string_agg(
             CAST (CASE ... END AS text),
             ''
          )
       AS numeric)
FROM ...

answered Nov 10, 2020 at 16:44

Laurenz Albe

257k22 gold badges314 silver badges390 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgresql: Concatenating regexp_split_to_table into a single string

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related