How to get substring from a sql table?

Question

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

are you sure that the question is for postgresql? cause there is no charindex() function per postgres documentation (postgresql.org/docs/current/static/functions-string.html) — Rahul
– Rahul, Commented Aug 12, 2014 at 19:13

Neil McGuigan · Accepted Answer · 2016-03-10 07:44:02Z

4

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

edited Mar 10, 2016 at 7:44

answered Aug 12, 2014 at 19:12

Neil McGuigan

48.5k12 gold badges130 silver badges156 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

FuzzyTree Over a year ago

shouldn't it be 2 instead of 3?

Rahul Over a year ago

I doubt the question is for postgresql but if it's then nice answer.

Neil McGuigan Over a year ago

@FuzzyTree it would parse the string like this: /randomCharacters/123456/randomNumbers = 1/2/3/4 , due to the leading '/'

Community · Accepted Answer · 2017-05-23 11:45:16Z

3

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.

edited May 23, 2017 at 11:45

CommunityBot

11 silver badge

answered Aug 12, 2014 at 19:57

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Collectives™ on Stack Overflow

How to get substring from a sql table?

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related