Postgres Query to Check a String Is a Number

Postgres query to check a string is a number

I think the easiest way would be a regular expression match:

select '12.41212' ~ '^[0-9\.]+$'
=> true

select 'Service' ~ '^[0-9\.]+$'
=> false

isnumeric() with PostgreSQL

As you may noticed, regex-based method is almost impossible to do correctly. For example, your test says that 1.234e-5 is not valid number, when it really is. Also, you missed negative numbers. What if something looks like a number, but when you try to store it it will cause overflow?

Instead, I would recommend to create function that tries to actually cast to NUMERIC (or FLOAT if your task requires it) and returns TRUE or FALSE depending on whether this cast was successful or not.

This code will fully simulate function ISNUMERIC():

CREATE OR REPLACE FUNCTION isnumeric(text) RETURNS BOOLEAN AS $$
DECLARE x NUMERIC;
BEGIN
    x = $1::NUMERIC;
    RETURN TRUE;
EXCEPTION WHEN others THEN
    RETURN FALSE;
END;
$$
STRICT
LANGUAGE plpgsql IMMUTABLE;

Calling this function on your data gets following results:

WITH test(x) AS ( VALUES (''), ('.'), ('.0'), ('0.'), ('0'), ('1'), ('123'),
  ('123.456'), ('abc'), ('1..2'), ('1.2.3.4'), ('1x234'), ('1.234e-5'))
SELECT x, isnumeric(x) FROM test;

    x     | isnumeric
----------+-----------
          | f
 .        | f
 .0       | t
 0.       | t
 0        | t
 1        | t
 123      | t
 123.456  | t
 abc      | f
 1..2     | f
 1.2.3.4  | f
 1x234    | f
 1.234e-5 | t
 (13 rows)

Not only it is more correct and easier to read, it will also work faster if data was actually a number.

Postgres Query: finding values that are not numbers

get the records which have no digits, then you would have to use the following regex:

DELETE FROM myrecords WHERE record ~ '^[^0-9]+$';

Here, the ^ character outside of square brackets means the beginning of the field, the $ character means the end of the field, and we require that all characters in between are non-digits. + indicates that there should be at least one such characters. If we would also allow empty strings, then the regex would look like ^[^0-9]*$.

If you want the records which would include digits and lower-case letter, then I would expect a regex like:

DELETE FROM myrecords WHERE record ~ '[0-9a-z]';

Postgresql - How to get numeric value from the end of string and string without end numeric value

You could use regexp_replace() and substring():

select
    regexp_replace(username, '\d+$', '') str,
    coalesce(substring(username from '\d+$')::int, 0) num
from mytable

Demo on DB Fiddlde:

with mytable as (
    select 'test' username
    union all select '123test'
    union all select '123test456'
    union all select 'test123'
    union all select 'test45test'
    union all select 'test55test55'
)
select
    regexp_replace(username, '\d+$', '') str,
    coalesce(substring(username from '\d+$')::int, 0) num
from mytable

str        | num
:--------- | --:
test       |   0
123test    |   0
123test    | 456
test       | 123
test45test |   0
test55test |  55

SQL LIKE condition to check for integer?

That will select (by a regex) every book which has a title starting with a number, is that what you want?

SELECT * FROM books WHERE title ~ '^[0-9]'

if you want integers which start with specific digits, you could use:

SELECT * FROM books WHERE CAST(price AS TEXT) LIKE '123%'

or use (if all your numbers have the same number of digits (a constraint would be useful then))

SELECT * FROM books WHERE price BETWEEN 123000 AND 123999;

Extract numbers from a field in PostgreSQL

Simply:

SELECT NULLIF(regexp_replace(po_number, '\D','','g'), '')::numeric AS result
FROM   tbl;

\D being the class shorthand for "not a digit".

And you need the 4th parameter 'g' (for "globally") to replace all occurrences.

Details in the manual.

For a known, limited set of characters to replace, plain string manipulation functions like replace() or translate() are substantially cheaper. Regular expressions are just more versatile, and we want to eliminate everything but digits in this case. Related:

• Regex remove all occurrences of multiple characters in a string
• PostgreSQL SELECT only alpha characters on a row
• Is there a regexp_replace equivalent for postgresql 7.4?

But why Postgres 8.4? Consider upgrading to a modern version.

Consider pitfalls for outdated versions:

• Order varchar string as numeric
• WARNING: nonstandard use of escape in a string literal

PostgreSQL 9.3: isnumeric() in a condition

You don't need a select (and it's actually wrong, as the error indicates) - just call isnumeric directly.
Also, by the way, your function is missing a return statement.

To sum it all up:

create or replace function tm(var text)
returns varchar as
$$
begin
    if (isnumeric(var)) then -- call isnumeric directly
        raise info 'Is numeric value';
    else
        raise info 'Not numeric';
    end if;
    return '0'; -- missing return value in the OP
end;
$$
language plpgsql;

pgSQL: select first occurrence of the number inside a string

You could use regexp_matches

CREATE table foo (
test VARCHAR);

INSERT INTO foo VALUES('abc-123a-66');

SELECT (regexp_matches(test, '\d+'))[1] FROM foo;

Example at SQLFiddle

How do I check if a string contains a number

Using a regular expression:

SELECT *
FROM test
WHERE REGEXP_LIKE(testcol, '[[:digit:]]');

Not using regular expressions:

SELECT *
FROM test
WHERE testcol LIKE '%0%'
    OR testcol LIKE '%1%'
    OR testcol LIKE '%2%'
    OR testcol LIKE '%3%'
    OR testcol LIKE '%4%'
    OR testcol LIKE '%5%'
    OR testcol LIKE '%6%'
    OR testcol LIKE '%7%'
    OR testcol LIKE '%8%'
    OR testcol LIKE '%9%'

---

Related Topics