This turned out to be a tricky puzzle which took up far more time than I thought it would - I guess for you too? :-).
I (finally) managed to solve it thus: First I created (using DDL and DML for those who wish to try this at home) a table called stringtest:
CREATE TABLE stringtest (my_string VARCHAR(255));
and populated it as below
INSERT INTO stringtest VALUES('/blah/moreblah/4556/stuff/file.name');
INSERT INTO stringtest VALUES('/blah/moreblah/stuff/3434/file.name');
INSERT INTO stringtest VALUES('/users/test/34343/sfdsfs/6778');
INSERT INTO stringtest VALUES('/users/test/34343/sfdsfs/677990');
Then I used regexp_matches as I thought would be natural
ntest=# SELECT (regexp_matches(my_string, '\d+')) FROM stringtest;
regexp_matches
----------------
{4556}
{3434}
{34343}
{34343}
(4 rows)
So, I thought, this is easy-peasy - over in two seconds. How wrong was I!!
I then tried all sorts of typecasts (chained as well as single!), functions and assorted Googling to no avail.
The key to this problem is to realise that regexp_matches returns an array (hence the little curly braces around the fields) of strings. So, PostgreSQL (unsurpsingly perhaps) can't convert an array of strings into an integer. More frantic Googling ensued and I came across this gem.
The key is the array_to_string function. The answer I came up with is this:
ntest=# SELECT ((array_to_string(regexp_matches(my_string, '\d+', 'i'), ';'))::integer) + 5
AS IntTest FROM stringtest;
inttest
---------
4561
3439
34348
34348
(4 rows)
The +5
isn't strictly necessary (note the change in values) - it was just a test to prove that they are indeed treated as INTEGER
s. Obviously, since they're account_ids
, you won't be performing arithmetic on them.
[EDIT]
Following the OP's comment below about adding a WHERE
clause, I tried the following. A simple WHERE
(...array_to_string = value...) doesn't appear to work.
Next, from here, I tried
ntest=# CREATE FUNCTION colint(stringtest)
ntest-# RETURNS integer AS
ntest-# $func$
ntest$# SELECT (array_to_string(regexp_matches(my_string, '\d+', 'i'), ';'))::integer
ntest$# FROM stringtest
ntest$# $func$ LANGUAGE SQL STABLE;
CREATE FUNCTION
but, that doesn't work - appears to be a bug in PostgreSQL AFICS.
ntest=# select *, stringtest.colint from stringtest;
my_string | colint
-------------------------------------+--------
/blah/moreblah/4556/stuff/file.name | 4556
/blah/moreblah/stuff/3434/file.name | 4556
/users/test/34343/sfdsfs/6778 | 4556
/users/test/34343/sfdsfs/677990 | 4556
(4 rows)
Definitely bad - same value for all of my function's derived values.
So, next I tried here using a CTE
and bingo!
WITH tab AS
(
SELECT *, (array_to_string(regexp_matches(my_string, '\d+', 'i'), ';'))::integer AS t
FROM stringtest
)
SELECT * FROM tab
WHERE tab.t = 3434;
Result:
my_string | t
-------------------------------------+------
/blah/moreblah/stuff/3434/file.name | 3434
(1 row)
Seems like a bit of a hack, but it gets the job done! :-) If you only want
specific columns in stringtest, name the columns individually in tab - e.g.
ntest=# WITH tab AS
ntest-# (
ntest(# SELECT my_string, (array_to_string(regexp_matches(my_string, '\d+', 'i'), ';'))::integer AS t
ntest(# FROM stringtest
ntest(# )
ntest-# SELECT my_string FROM tab
ntest-# WHERE tab.t = 3434;
my_string
-------------------------------------
/blah/moreblah/stuff/3434/file.name
(1 row)
Final thought, it might be just as well to have a TRIGGER
on the table and do an INSERT
into an additional field of just the account_id
integer - that would make it very efficient for searching - it's a pity that PostgreSQL's [persisted | calculated | computed | generated] field functionality isn't up to the job - they are pretty cool when used correctly.
[FINAL EDIT]
I was thinking that this shouldn't be that tricky - and then I pondered Occam's razor - don't make things more complicated than they have to be - or K.I.S.S.. Would you believe that this works a treat?
CREATE VIEW vtest AS
SELECT *, (SELECT ((array_to_string(regexp_matches(my_string, '\d+', 'i'), ';'))::integer)
AS account_id)
FROM stringtest;
Now the view:
ntest=# select * from vtest;
my_string | account_id
-------------------------------------+------------
/blah/moreblah/4556/stuff/file.name | 4556
/blah/moreblah/stuff/3434/file.name | 3434
/users/test/34343/sfdsfs/6778 | 34343
/users/test/34343/sfdsfs/677990 | 34343
(4 rows)
And, selecting from same with WHERE
clause:
ntest=# select * from vtest where account_id = 4556;
gives (gulp... after all that! :-) )
my_string | account_id
-------------------------------------+------------
/blah/moreblah/4556/stuff/file.name | 4556
(1 row)