CODEWITHSUNDEEP
sqlregexpostgresqlsubstring
user3814846
user3814846

Reputation:

Return Only number from a string if it contains numbers

For example,
string is abc123CD need to find out a method to read only numbers in the string
i.e. 

  select a_postgres_function('abc123CD')

  ------
  Result
  ------
  123

My try

 select substring('abc123CD' from '%#"^[0-9]+$#"%' for '#')

Upvotes: 0

Views: 163

Answers (3)

pozs
pozs

Reputation: 36274

If you want to get all digit characters from the string (not just the first group), it is easier to remove all characters, which aren't a digit:

select regexp_replace('abc123CD45ef', '[^\d]+', '', 'g');

-- regexp_replace
-- --------------
--    '12345'

Upvotes: 0

Vivek S.
Vivek S.

Reputation: 21993

As per ntalbs's Answer

Wrap that query into a Function

create or replace function shownums(text) returns integer as 
$$
select (regexp_matches($1,'\d+'))[1]::int; 
$$
language sql;

Upvotes: 0

ntalbs
ntalbs

Reputation: 29468

Try this:

select (regexp_matches('abc123CD', '\d+'))[1];

Since regexp_matches returns array of text, you should access the first element by [1].

Upvotes: 1

Related Questions