-4

Column

1

7

f

3

2

c

1

d

6

4

e

g

b

I want to be able to filter this using the IN() operator in the where clause and pull out only the numbers. The column is a varchar so it is coming back as an error in postgres

UserX
  • 157
  • 13
  • 1
    [Postgres query to check a string is a number](https://stackoverflow.com/questions/19975257/postgres-query-to-check-a-string-is-a-number) – Lukasz Szozda Aug 09 '17 at 16:48
  • Have you tried `in('0','1','2','3','4','5','6','7','8','9')` ? – fen1x Aug 09 '17 at 16:48
  • Yes I get an error say the column is not INT and varchar. This is an example of what I am trying to do, the actual data has 30 k rows – UserX Aug 09 '17 at 16:52

3 Answers3

0

select substring(colname FROM '[0-9]+') from tablename

NS009
  • 7
  • 1
  • 1
  • 5
  • Still getting a error ERROR: operator does not exist: character varying = integer – UserX Aug 09 '17 at 16:55
  • try this one also select regexp_replace(name , '[^0-9]*', '', 'g') from tablename; – NS009 Aug 09 '17 at 16:59
  • 3
    You should explain a bit about WHY you think your answer is correct. – Difster Aug 09 '17 at 17:11
  • create table contacts(id int, name varchar(200)); insert into contacts(id, name) values(1, 'abc 123 cde 555 mmm 999'); select regexp_replace(name , '[^0-9]*', '', 'g') from contacts; – NS009 Aug 09 '17 at 17:27
0

You can filter the numbers using the ISNUMERIC() function on the WHERE Clausule.

Something like this:

SELECT *
FROM Table1
WHERE ISNUMERIC(column_name)=1

As mentioned on the comments, this is for SQL Server, but you can create your own ISNUMERIC function in PostgreSQL following this example:

isnumeric() with PostgreSQL

Doliveras
  • 1,794
  • 2
  • 14
  • 30
  • I get this error: ERROR: function isnumeric(character varying) does not exist – UserX Aug 09 '17 at 17:09
  • @RPr my bad, I did not see that you're using PostgreSQL and The code I proposed is for SQL Server. You can create your own isnumeric function in PostgreSQL using this https://stackoverflow.com/questions/16195986/isnumeric-with-postgresql – Doliveras Aug 09 '17 at 17:13
0

I ended up subquerying with this in the SELECT --- cast(substring(column FROM '[0-9]+') as int) and this in the WHERE column ~ '^\d+$' in the FROM as its own table. Pulling just the integers i needed from that with IN (1,2,3)

UserX
  • 157
  • 13