Postgresql: extract number before given substring

Question

I want to create variable: Number which extract numbers before $

Name      | Number
abc1/12 $ | 12
12av 12$  | 12
114$-bgv  | 114

I have a query:

select Name, substring(Name FROM '%#"[0-9]+#"$%' FOR '#') as Number
from table;

But it returns:

Name      | Number
abc1/12 $ | 
12av 12$  | 2
114$-bgv  | 4

Any ideas?

it does not work. and additionaly if I want to extract from pattern 'number+'gb' (i.e. 15gb) instead of number+$ (i.e. 15$) it returns 'invalid escape \ sequence'; using postgresql 9.2 — user3463225
– user3463225, Commented May 23, 2016 at 15:36
"It does not work" is a bad description of the problem. And be more specific about what exactly you want to extract. Additionally it seems that you unfamiliar with regular expressions (I am unfamiliar too :) Learn more about its syntax. In short: dollar sign '$' should be escaped by slash because it means EOL in regular expressions. However gb is a simple substring so it is not needed to be escaped: '(\d+)\s*gb. Thats why you getting 'invalid escape sequence' error. Good luck. — Abelisto
– Abelisto, Commented May 23, 2016 at 16:11

Nick · Accepted Answer · 2016-05-23 16:01:32Z

1

Use regexp_matches():

select
  (regexp_matches(Name,  '^(.*[^\d]+){0,1}(\d+)\$'))[2] as Number
from (
  select 'abc1/12$' as Name
  union select '12av 12$'
  union select 'bgv-114$'
  union select '124$'
) t

Or with substring()

select
  coalesce(substring(Name FROM '.*[^\d]+(\d+)\$'), '') ||
  coalesce(substring(Name FROM '^(\d+)\$'), '') as Number
from (
  select 'abc1/12$' as Name
  union select '12av 12$'
  union select 'bgv-114$'
  union select '124$'
) t

edited May 23, 2016 at 16:01

answered May 23, 2016 at 13:08

Nick

10.2k8 gold badges65 silver badges97 bronze badges

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

2 Comments

Nuri Tasdemir Over a year ago

This does not handle '5$ foo'

Nick Over a year ago

More transparent query is with using regexp_matches().

Collectives™ on Stack Overflow

Postgresql: extract number before given substring

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related