Search code examples
sqlregexpostgresqlpostgresql-9.3

How to extract invoice number between - deliminiters

Invoice numbers can contain 1 or minus characters in beginnig and 1 or more -n version numbers in end. How to extract main part of invoice number ? For this leading - characters should stripped and trailing -n variants should also stripped.

For example

 1002546556
 -1002546556
 ---1002546556
 1002546556-1-K
 -1002546556-2
 --1002546556-2-3

for all those numbers result should be 

1002546556

I tried

select regexp_replace(invoicenumber, '-.*', '', 'g') from invoice

but if invoice number starts with - , it returns empty value.

Using

PostgreSQL 9.3.5, compiled by Visual C++ build 1600, 64-bit

Solution

  • There is no need for regular expressions really. This will do for example:
    select split_part(ltrim(invoicenumber,'-'),'-',1)