How to simplify postgres regexp_replace

postgres regex examples
postgresql regexp_replace special characters
postgres substring
postgres replace
postgres regexp_replace flags
regexp_replace redshift
postgres regexp_matches in where clause
postgresql regexp_replace non greedy

Is there a way to simplify this query using only one regexp_replace?

select regexp_replace(regexp_replace('BL 081', '([^0-9.])', '', 'g'), '(^0+)', '', 'g')

the result should be 81

I'm trying to remove all non-numeric chars and leading 0's from the result

Why not just change the range from 0-9 to 1-9?

regexp_replace('BL 081', '(^[^1-9]+)', '', 'g')

PostgreSQL REGEXP_REPLACE Function By Examples, This tutorial shows you how to use the PostgreSQL REGEXP_REPLACE() function to replace substrings that match a regular expression with a new substring. TheAnswerHub is a top destination for finding answers online. Browse our content today! Find database postgresql on TheAnswerHub.com.

You can do this by capturing the digits you want (not including any leading zeros) and removing everything else:

select regexp_replace('BL 0081', '.*?([1-9][0-9]*)$', '\1')

Output

81

Note you don't need the g flag as you are only making one replacement.

Demo on dbfiddle

Regular Expressions in PostgreSQL, We will start with demonstrations of PostgreSQL SQL statements that find The ones we commonly use are ~, regexp_replace, and regexp_matches. Regular expressions are much richer than our simplified view of things. Find Postgres As A Service. Find Quick Info from Multiple Sources

This pattern should do: \D+|(?<=\s)0+

\D - matches characters that are not digits
(?<=\s) - looks behind for spaces and matches leading zeros

You can use 1 fewer regexp_replace:

select regexp_replace('BL 081', '\D+|(?<=\s)0+', '', 'g')
# outputs 81

alternatively, if you are interested in the numeric value, you could use a simpler regex and then cast to an integer.

select regexp_replace('BL 081', '\D+', '')::int
# also outputs 81, but its type is int

regex - 如何简化postgres regexp_replace, How to simplify postgres regexp_replace. Is there a way to simplify this query using only one regexp_replace? select regexp_replace(regexp_replace('BL 081', � Azure PostgreSQL - A Managed PostgreSQL Database Service for App Developers.

Pattern matching with LIKE, SIMILAR TO or regular expressions in , Beginning with PostgreSQL 9.1 you can facilitate the extension pg_trgm to provide if you should happen to search for adjacent initials, you can simplify further: Buy Postgres Performance at Amazon. Free Shipping on Qualified Orders.

Postgres regex special characters, REGEXP_REPLACE is similar to the TRANSLATE function and the Simplified regular expressions are composed of: Sets of possible characters like [a-z_. The regexp_replace function provides substitution of new text for substrings that match POSIX regular expression patterns. It has the syntax regexp_replace(source, pattern, replacement [, flags]). The source string is returned unchanged if there is no match to the pattern.

Documentation: 9.4: Pattern Matching, The regexp_replace function provides substitution of new text for substrings that match POSIX regular expression patterns. It has the syntax regexp_replace� Besides the REPLACE() and REGEXP_REPLACE() functions, PostgreSQL provides you with another function named TRANSLATE() for string substitution. Given a setof characters, the TRANSLATE() function replaces any characters in the source string that match the set with the characters in the new_set.

Comments
  • Your reference method turns 'BL 081-you sunk my battleship-097' into '81097'. Is that the desired outcome?
  • Yes that is just fine
  • What if the value is BL 650?
  • @Nick good point, then with the revision I put it in, that will give 650
  • Yeah, this is good although you don't need the enclosing ().
  • When I run your dbfiddle, I get '1', not '81'. You need to make the .* non-greedy with a question mark.
  • @jjanes thanks for picking that up - I altered the regex slightly and didn't double check the results.
  • So instead of replacing everything that is not a number or leading zero with an empty string and using 'g' flag, you are selecting everything that should be in the result with the regex and are using \1 to store the match in the replacement string. Thanks, it works great
  • @MyRealNameIsBlaze exactly. You should also check out mikek's answer which might well perform better.
  • After testing it mikek's answer does run faster: Execution Time: 9.528 ms vs Execution Time: 33.374 ms