Re: Regexp + spaces PG 9.1

2017-05-31 16:34 GMT+12:00 David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>:

> On Tue, May 30, 2017 at 9:17 PM, Patrick B <patrickbakerbr(at)gmail(dot)com>
> wrote:
>
>>
>> regexp_matches(name, '((main|medium).name/\d+.\d+)') as filename,
>>
>>
>> Example here: http://sqlfiddle.com/#!15/5f4f0/4
>>
>> 1. can only get the jpg file name
>>
>> ​Because those are the only two file names consisting of one or more
> numbers, something else, and ending with one or more numbers
>
> I'm pretty sure you mean for the "." in that regex to be "\." so it is
> treated as the period before the extension and not the regex "any" meta
> character.
>
> \d means number, 1-9. You probably want something like "[\d\w\s]+" to
> match digits, letters (including underscore), and white-space. Or maybe
> "[^\r\n/\.]+" - specify what it is that won't be in the file name.
>
>>
>> 1. I don't get only the file name but the rest as well, which is not
>> what I need
>>
>> ​You get more than just "the rest"...you get an array with two entries
> corresponding to the two parenthetical captures (the fiddle apparently
> doesn't display the "{}" that would make this much more obvious).
>
> (regexp_matches​(...))[#]
>
> The above will let you extract only the array position that you specify.
> You will need to add more parens to delimit exactly what you need.
>
> You can use "(?:regex)" instead of normal parens to group without
> capturing.
>
> David J.
>
>

Thanks David! That helped.

See example:
http://dbfiddle.uk/?rdbms=postgres_9.6&fiddle=a74cd219191444f0228b9df581548a37

Will do some more tests but i think that's all i need. Cheers
Patrick