CODEWITHSUNDEEP
sqldatabasepostgresqlpattern-matching
johncorser
johncorser

Reputation: 9842

How to get substring from a sql table?

So I have a column (called account_uri) in a postgres table that looks like this:

/randomCharacters/123456/randomNumbers

I need to query for the substring in the middle, which is a string of characters between two / symbols.

My current attempt looked like this:

SELECT 
REVERSE(SUBSTRING(REVERSE([account_uri]),0,CHARINDEX('/',REVERSE(account_uri))))
FROM exp_logs
LIMIT 15

Which selects only the randomNumbers and not the desired numbers.

I tried to build on that idea though and used

(SUBSTRING(REVERSE(SUBSTRING(REVERSE([account_uri]),CHARINDEX('/',REVERSE(account_uri)))),1,CHARINDEX('/',REVERSE(SUBSTRING(REVERSE([account_uri]),CHARINDEX('/',REVERSE(account_uri)))))))

but that only returns a bunch of / symbols and no numbers at all.

If anyone can help me query for this substring, I would be immensely grateful

Upvotes: 1

Views: 535

Answers (2)

Neil McGuigan
Neil McGuigan

Reputation: 48287

select
  split_part(account_url, '/', 3)
from exp_logs;

works.

http://www.postgresql.org/docs/9.3/static/functions-string.html

Compatibility: 8.3+

Fiddle: http://sqlfiddle.com/#!15/5e931/1

Upvotes: 4

Erwin Brandstetter
Erwin Brandstetter

Reputation: 659207

A couple of solutions, sorted by fastest first:

SELECT split_part(account_uri, '/', 3)        AS solution_1  -- Neil's solution
      ,substring(account_uri,'^/.*?/(.*?)/')  AS solution_2
      ,substring(account_uri,'^/[^/]*/(\d*)') AS solution_3
      ,(string_to_array(account_uri,'/'))[3]  AS solution_4
FROM (
   VALUES
     ('/randomCharacters/123456/randomNumbers')
    ,('/v o,q9063´6qu/24734782/2369872986')
    ,('/DFJTDZTJ/1/234567')
    ,('/ ijgtoqu29836UGB /999/29672')
   ) exp_logs(account_uri);

@Neil's solution proved fastest in a quick test on a table with 30k rows.

Upvotes: 3

Related Questions