Обсуждение: [GENERAL] Regexp + spaces PG 9.1
Hi guys,
I've got a column which stores the file name on it, the column is character varying(255). I'm selecting that value in a CTE query; basically:
test1 AS (SELECTregexp_matches(name, '((main|medium).name/\d+.\d+)') as filename,*from test1;)selectfilename[1]from test1
Example here: http://sqlfiddle.com/#!
As you can see on the example:
- if the file is a image (jpg), then it will have 2 variations (main|medium).
- If the file is a pdf, then it will only have 1 variation (main).
I basically need a regexp_matches expression that only gets me the file name, after the main.name/ for example.
On the example I gave there are 2 problems:
- I can only get the jpg file name
- I don't get only the file name but the rest as well, which is not what I need
How to do that?
Thanks!
Patrick
regexp_matches(name, '((main|medium).name/\d+.\d+)') as filename, Example here: http://sqlfiddle.com/#!15/5f4f0/4
- 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.
- 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.
2017-05-31 16:34 GMT+12:00 David G. Johnston <david.g.johnston@gmail.com>:
regexp_matches(name, '((main|medium).name/\d+.\d+)') as filename, Example here: http://sqlfiddle.com/#!15/5f4f0/4
- 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 numbersI'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.
- 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.
Will do some more tests but i think that's all i need. Cheers
Patrick