PostgreSQL - finding string using regular expression

Question

What I am looking to do is to, within Postgres, search a column for a string (an account number). I have a log table, which has a parameters column that takes in parameters from the application. It is a paragraph of text and one of the parameters stored in the column is the account number.

The position of the account number is not consistent in the text and some rows in this table have nothing in the column (since no parameters are passed on certain screens). The account number has the following format: L1234567899. So for the account number, the first character is a letter and then it is followed by ten digits.

I am looking for a way to extract the account number alone from this column so I can use it in a view for a report.

So far what I have tried is getting it into an array, but since the position changes, I cannot count on it being in the same place.

select foo from regexp_split_to_array(
(select param from log_table where id = 9088),  E'\\s+') as foo

James Douglas · Accepted Answer · 2018-01-02 20:10:21Z

1

You can use regexp_match() to achieve that result.

(regexp_match(foo,'[A-Z][0-9]{10}'))[1]

DBFiddle

answered Jan 2, 2018 at 20:10

James Douglas

3,4662 gold badges25 silver badges43 bronze badges

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

Comments

ztaylor54 · Accepted Answer · 2018-01-02 20:05:26Z

0

Use substring to pull out the match group.

select substring ('column text' from '[A-Z]\d{10}')

Reference: PostgreSQL regular expression capture group in select

answered Jan 2, 2018 at 20:05

ztaylor54

4064 silver badges15 bronze badges

Collectives™ on Stack Overflow

PostgreSQL - finding string using regular expression

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related