CODEWITHSUNDEEP
regexstringpostgresql
ProgramBrah
ProgramBrah

Reputation: 13

PostgreSQL - finding string using regular expression

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

Upvotes: 0

Views: 1346

Answers (2)

James Douglas
James Douglas

Reputation: 3446

You can use regexp_match() to achieve that result.

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

DBFiddle

Upvotes: 1

ztaylor54
ztaylor54

Reputation: 406

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

Upvotes: 0

Related Questions