Makes sense.
Thank you both for clarifications!
Was only wondering if this feature is there so I could elegantly do
equivalent of perl's (small 'a', anycase 'sd', small 'f'):
$,="\n";
my $testr='1asdf
2AsdF
3AsDF
4asDf
5aSDf
6aSdf
7ASdf
8Asdf';
my @res = $testr =~ /a(?i:sd)f/g;
print @res;
-
a
matshyeq writes:
> I can see postgresql claims to supports regular expression flags:
Yup.
> What I find don't makes sense to me is that those flags could be used to
> switch on/off match feature locally as opposed to the whole regex (same as
> though flags parameters of regex functions).
Not al
On Sun, Jan 3, 2016 at 8:49 AM, matshyeq wrote:
> Does that mean these "EMBEDDED OPTIONS" can be only defined at the
> beginning of the pattern and therefore don't offer anything extra over *flags
> *option?
>
Yes, this is how they behave. The most important difference is that:
column ~ 'rege
On Tue, Dec 29, 2015 at 2:26 PM, Michael Nolan wrote:
> On Mon, Dec 28, 2015 at 2:08 PM, Christopher Molnar
> wrote:
>
>> Hello all!
>>
>> Sorry to have to ask the experts here for some regex assistance again. I
>> am admittadly awful with these and could use some help.
>>
>> Any suggestions?
>>
On Mon, Dec 28, 2015 at 2:08 PM, Christopher Molnar
wrote:
> Hello all!
>
> Sorry to have to ask the experts here for some regex assistance again. I
> am admittadly awful with these and could use some help.
>
> Any suggestions?
>
I have found over the years that it is far easier to write a short
## Melvin Davidson (melvin6...@gmail.com):
> UPDATE pcilms_assign
>SET intro = REPLACE (intro, 'HVACR1114_LAB_13A.pdf',
> '&file=HVACR1114_LAB_13A.pdf')
> WHERE intro like 'https://owncloud.porterchester.edu%'
> AND course=18 and id=55413;
Unfortunately, that tries to do the right th
## Christopher Molnar (cmolna...@gmail.com):
> I have tried something like:
>
> update pcilms_assign set intro=regexp_replace(intro, '/([^/]*)\" title=$',
> '&files=\1') where intro like '%https://owncloud.porterchester.edu%' and
> course=18 and id=55413;
http://blog.codinghorror.com/parsing-ht
Chris,
First, even though it may or may not apply in this instance, it is ALWAYS a
good idea (and good manners) to provide the PostgreSQL version and your O/S
when posting to this board.
I have also previously replied that the following should work:
UPDATE pcilms_assign
SET intro = REPLACE (i
Thank you Felix that was exactly what I needed!
-Chris
On Mon, Dec 28, 2015 at 2:23 PM, Félix GERZAGUET
wrote:
> Hello Chris,
>
> On Mon, Dec 28, 2015 at 8:10 PM, Christopher Molnar <
> cmol...@ourworldservices.com> wrote:
>
>> Any suggestions?
>>
> This seems to works:
>
> select regexp_replac
Although, in this particular case, it is not version or O/S specific, it is
generally a good policy (and manners) to state them whenever contacting
this mail list.
In that way, future users that refer back to problems have it documented as
to which are and are not version specific.
On Mon, Dec 28,
On Mon, Dec 28, 2015 at 12:25 PM, Melvin Davidson
wrote:
> Will this work?
>
> UPDATE your_table
>SET your_column = REPLACE (your_column, 'HVACR1114_LAB_13A.pdf',
> '&file=HVACR1114_LAB_13A.pdf')
> WHERE ;
>
> Your mileage may vary because you have not stated your VERSION of
> PostgreSQL or
On Mon, Dec 28, 2015 at 12:10 PM, Christopher Molnar <
cmol...@ourworldservices.com> wrote:
Given this...
> 'Complete the attached lab and submit via dropbox\rhttps://owncloud.porterchester.edu/HVACR/PCI_GasHeat/GasElectrical/HVACR1114_LAB_13A.pdf";
> title="Lab 13A">Lab 13A<\a>'
>
I have no cl
Will this work?
UPDATE your_table
SET your_column = REPLACE (your_column, 'HVACR1114_LAB_13A.pdf',
'&file=HVACR1114_LAB_13A.pdf')
WHERE ;
Your mileage may vary because you have not stated your VERSION of
PostgreSQL or your O/S.
On Mon, Dec 28, 2015 at 2:10 PM, Christopher Molnar <
cmol...@ou
Hello Chris,
On Mon, Dec 28, 2015 at 8:10 PM, Christopher Molnar <
cmol...@ourworldservices.com> wrote:
> Any suggestions?
>
This seems to works:
select regexp_replace('Complete the attached lab and submit via
dropbox\rhttps://owncloud.porterchester.edu/HVACR/PCI_GasHeat/GasElectrical/HVACR1114_
Thanks for your help!
I updated to version 9.3 and now it's working.
Janek Sendrowski
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Janek Sendrowski wrote:
> My current version is 9.2. I could just update it.
> I got the pg_trgm from here:
> http://git.postgresql.org/gitweb/?p=postgresql.git;a=tree;f=contrib/pg_trgm;hb=refs/heads/master
Get a production release version from the snapshot tarball
downloads or use a URL that l
On 11/18/2013 08:32 AM, Janek Sendrowski wrote:
Hi,
My current version is 9.2. I could just update it.
I got the pg_trgm from here:
http://git.postgresql.org/gitweb/?p=postgresql.git;a=tree;f=contrib/pg_trgm;hb=refs/heads/master
And the regex files from here:
http://git.postgresql.org/gitweb/?p=p
Hi,
My current version is 9.2. I could just update it.
I got the pg_trgm from here:
http://git.postgresql.org/gitweb/?p=postgresql.git;a=tree;f=contrib/pg_trgm;hb=refs/heads/master
And the regex files from here:
http://git.postgresql.org/gitweb/?p=postgresql.git;a=tree;f=src/include/reg
Adrian Klaver writes:
> On 11/18/2013 07:34 AM, Janek Sendrowski wrote:
>> Have some issues to built the pg_trgm module from source.
> FYI I find those files in the source I downloaded from the Postgres site:
Sounds like Janek is trying to build 9.3 pg_trgm against a pre-9.3
server installation.
On 11/18/2013 07:34 AM, Janek Sendrowski wrote:
Hi,
Have some issues to built the pg_trgm module from source.
For first the regexport.h file was missing in /usr/include, so I got it.
Now I still need the the regexport.c file and probably also the other one
You can see the files in this link:
htt
> On 2013-04-25, Karsten Hilbert wrote:
>> On Thu, Apr 25, 2013 at 10:32:26AM -0400, Tom Lane wrote:
>>
>>> Karsten Hilbert writes:
>>> > What I don't understand is: Why does the following return a
>>> > substring ?
>>>
>>> > select substring ('junk $$ junk' from
>>> '\$<[^<]+?::[^:]+?>\$');
>>
On 2013-04-25, Karsten Hilbert wrote:
> On Thu, Apr 25, 2013 at 10:32:26AM -0400, Tom Lane wrote:
>
>> Karsten Hilbert writes:
>> > What I don't understand is: Why does the following return a
>> > substring ?
>>
>> >select substring ('junk $$ junk' from
>> > '\$<[^<]+?::[^:]+?>\$');
>>
>>
Karsten Hilbert writes:
> I would have thought "<[^<]+?:" should mean:
> match a "<"
> followed by 1-n characters as long as they are not "<"
> until the VERY NEXT ":"
> The "?" should make the "+" after "[^<]" non-greedy and thus
> stop at the first occurrence of ":", right ?
On Thu, Apr 25, 2013 at 03:40:51PM +0100, Thom Brown wrote:
> On 25 April 2013 15:32, Tom Lane wrote:
> > Karsten Hilbert writes:
> >> What I don't understand is: Why does the following return a
> >> substring ?
> >
> >> select substring ('junk $$ junk' from
> >> '\$<[^<]+?::[^:]+?>\$');
On Thu, Apr 25, 2013 at 10:32:26AM -0400, Tom Lane wrote:
> Karsten Hilbert writes:
> > What I don't understand is: Why does the following return a
> > substring ?
>
> > select substring ('junk $$ junk' from
> > '\$<[^<]+?::[^:]+?>\$');
>
> There's a perfectly valid match in which [^<]+? m
On 25 April 2013 15:32, Tom Lane wrote:
> Karsten Hilbert writes:
>> What I don't understand is: Why does the following return a
>> substring ?
>
>> select substring ('junk $$ junk' from
>> '\$<[^<]+?::[^:]+?>\$');
>
> There's a perfectly valid match in which [^<]+? matches allergy::test
>
Karsten Hilbert writes:
> What I don't understand is: Why does the following return a
> substring ?
> select substring ('junk $$ junk' from
> '\$<[^<]+?::[^:]+?>\$');
There's a perfectly valid match in which [^<]+? matches allergy::test
and [^:]+? matches 99.
rega
On 12 February 2012 18:49, Tom Lane wrote:
> Thom Brown writes:
>> What am I missing?
>
> I might be more confused than you, but I think you're supposing that
> the result of ascii(E'\\1') has something to do with the match that
> the surrounding regexp_replace function will find, later on when i
On Feb 12, 2012, at 13:26, Thom Brown wrote:
> Hi,
>
> Could someone explain the following behaviour?
>
> SELECT regexp_replace(E'Hello & goodbye ',E'([&])','&#' ||
> ascii(E'\\1') || E';\\1');
>
> This returns:
>
> regexp_replace
>
> Hello \& goodbye
> (1 row)
>
Thom Brown writes:
> What am I missing?
I might be more confused than you, but I think you're supposing that
the result of ascii(E'\\1') has something to do with the match that
the surrounding regexp_replace function will find, later on when it
gets executed. The actual arguments seen by regexp_
>
> Not testing here but... and ignore whitespace
>
> '^( [ \[\( ]? \s* \d{3} \s* [ -\s\]\) ] \d{3} [ -\s ] \d{4} )$'
>
Some tweaks needed but seriously consider dropping RegEx and going the
functional index route.
> '^( [ \[\( ]? \s* \d{3} \s* [ -\s\]\) ] \s* \d{3} \s* [ -\s ] \s* \d{4} )$'
Now that I read more closely the alternation is actually concatenation. My
point still stands but your issue is that you have not created a functional
index on the decryption result of the encrypted phone number. PostgreSQL does
not know that the decrypted phone number is equivalent to the une
Naoko Reeves writes:
> Also forgot to mentioned the version:
> select version() >> "PostgreSQL 8.4.6 on i386-apple-darwin, compiled by GCC
> i686-apple-darwin8-gcc-4.0.1 (GCC) 4.0.1 (Apple Computer, Inc. build 5370),
Oh --- there's your problem. In 8.4 and earlier, we don't trust \( to be
a lite
On Aug 11, 2011, at 18:26, Naoko Reeves wrote:
> Hello,
> I have query phone number in database as follows:
> [123) 456-7890
>
> (123) 456-7890
>
Store phone numbers without formatting...the data is the numbers themselves the
formatting is presentation.
> When I query like this:
>
> SELECT
Tom,
Thank you for your quick reply. Data start with "(123" only returns 28
records where as phone number start with"[123" returns 1.
Changed the data so that both will return 1 row.
One with "(999" query takes about 30 seconds (30983ms) without index.
One with "[999" take about 28 ms with index.
Naoko Reeves writes:
> I have query phone number in database as follows:
> [123) 456-7890
> (123) 456-7890
> When I query like this:
> SELECT * FROM phone
> WHERE phone_number ~ ('^\[123' || '[-\s\)]{0,2}' || '456' || '[-\s\)]{0,2}'
> || '7890')
> it use Index but if I query like this (notice
On Thu, Sep 03, 2009 at 03:22:12PM -0700, Nick wrote:
> Im trying to get all the text before the '' tag.
>
> SELECT SUBSTRING('onetwothree','(^.*).*$');
>
> returns "onetwo"
>
> How do I get it to return "one"?
You can either use a non-greedy regex like this:
SELECT substring('onetwothree','(^
Quoting "A. Kretschmer" :
Try:
test=*# SELECT '1.1.1.1' ~ E'^\\d+';
Ag, of course, thanks Andreas.
Cheers
Henry
pgp5XMelkfQ4Y.pgp
Description: PGP Digital Signature
Henry schrieb:
I must be missing something here:
SELECT '1.1.1.1' ~ E'^\d+';
returns FALSE, when I would expect TRUE, as for:
SELECT '1.1.1.1' ~ E'^[[:digit:]]+';
ie, '[[:digit:]]' != '\d'
In config, "regex_flavor = advanced".
Any ideas?
Yes; you have to escape the backslash character:
In response to Henry :
> Greets,
>
> I must be missing something here:
>
> SELECT '1.1.1.1' ~ E'^\d+';
>
> returns FALSE, when I would expect TRUE, as for:
Try:
test=*# SELECT '1.1.1.1' ~ E'^\\d+';
?column?
--
t
(1 row)
Regards, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 03
On Thu, Jul 10, 2008 at 1:22 PM, Tom Lane <[EMAIL PROTECTED]> wrote:
> "Scott Marlowe" <[EMAIL PROTECTED]> writes:
>> ...Which is not surprising. It's greedy. So, I turn off the greediness
>> of the first + with a ? and then I get this
>
>> select substring (notes from E'LONG DB QUERY.+?time: [0-
"Scott Marlowe" <[EMAIL PROTECTED]> writes:
> ...Which is not surprising. It's greedy. So, I turn off the greediness
> of the first + with a ? and then I get this
> select substring (notes from E'LONG DB QUERY.+?time: [0-9]+.[0-9]+')
> from table where id=1;
> LONG DB QUERY (db1, 4.937628984451
On Feb 20, 2008, at 5:51 PM, Postgres User wrote:
Now to end my fixation, one last item. What about the case of a null
or empty param value- is there a way to assign a condition value that
Postgres will ignore when processing the query?
This syntax results in a seq scan: WHERE fielda = Coale
Tom,
I was looking for another approach but didn't come across that array
syntax in my searches (perhaps because it's newer. Thanks for a
solution.
Now to end my fixation, one last item. What about the case of a null
or empty param value- is there a way to assign a condition value that
Postgres
"Postgres User" <[EMAIL PROTECTED]> writes:
> My users are developers and the goal was to accept a simple
> comma-delimited list of string values as a function's input parameter.
> The function would then parse this input param into a valid regex
> expression.
Why are you fixated on this being a
Tino,
My users are developers and the goal was to accept a simple
comma-delimited list of string values as a function's input parameter.
The function would then parse this input param into a valid regex
expression.
I was trying to write a function that lets me avoid using Execute
and instead wr
Postgres User wrote:
im trying to allow the client to pass a varchar param into my
function, and want to avoid any parsing of the parameter inside the
function, or code to build a sql string.
if the function can use this code, it will be compiled and optimized
(unlike a dynamic sql stirng)
sele
On Wed, Feb 20, 2008 at 12:56:54AM -0800, Postgres User wrote:
> a final question: why does this syntax do a seq scan + filter:
>
> select * from tablea where fielda = fielda -or- select * from
> tablea where fielda in (fielda)
>
> while this syntax results in no filter, seq scan only
>
> sel
a final question: why does this syntax do a seq scan + filter:
select * from tablea where fielda = fielda -or- select * from
tablea where fielda in (fielda)
while this syntax results in no filter, seq scan only
select * from tablea where 1 = 1
it seems that both where clauses should be ignor
Postgres User wrote:
by the way, your example works fine unless it's a null value or empty string
unfortunately, postgres isn't smart enough to know that the when
p_param below is null, that the WHERE condition can be ignored
select * from table where name in (Coalesce(p_param, name))
which is
by the way, your example works fine unless it's a null value or empty string
unfortunately, postgres isn't smart enough to know that the when
p_param below is null, that the WHERE condition can be ignored
select * from table where name in (Coalesce(p_param, name))
which is the same as: select *
doh! tom, let me know if you decide to hack out a fix for this one of
these nights ;)
thanks for your help.
On Feb 19, 2008 9:45 PM, Tom Lane <[EMAIL PROTECTED]> wrote:
> "Postgres User" <[EMAIL PROTECTED]> writes:
> > Yes that works, but the whole point of the exercise is replace many OR
> > sta
"Postgres User" <[EMAIL PROTECTED]> writes:
> Yes that works, but the whole point of the exercise is replace many OR
> statements with 1 regex expression. So it's not what I'm looking for.
Unfortunately, Postgres is not as intelligent as you are. There is
no mechanism to rewrite a multi-branch r
im trying to allow the client to pass a varchar param into my
function, and want to avoid any parsing of the parameter inside the
function, or code to build a sql string.
if the function can use this code, it will be compiled and optimized
(unlike a dynamic sql stirng)
select * from mytable where
Postgres User wrote:
Yes that works, but the whole point of the exercise is replace many OR
statements with 1 regex expression. So it's not what I'm looking for.
Why do you want it done this way?
You can build an array of strings to check and use an in clause.
Using php :
$checks = array('A
Yes that works, but the whole point of the exercise is replace many OR
statements with 1 regex expression. So it's not what I'm looking for.
On Feb 19, 2008 9:16 PM, Chris <[EMAIL PROTECTED]> wrote:
> Postgres User wrote:
> > Thanks, my dumb mistake.
> > I need to perform the equivalent of a WHER
Postgres User wrote:
Thanks, my dumb mistake.
I need to perform the equivalent of a WHERE clause OR expression using
regex to match exact strings.
_
this example hits the index:
select * from eod where name ~ '^BA$'
but wh
Thanks, my dumb mistake.
I need to perform the equivalent of a WHERE clause OR expression using
regex to match exact strings.
_
this example hits the index:
select * from eod where name ~ '^BA$'
but when I try to add another
On Feb 19, 2008, at 9:32 PM, Postgres User wrote:
I'm running a simple query on 8.2. With this syntax, Explain indicate
that the index is scanned:
select * from eod where name = 'AA'
However, when I change the query to use simple regex:
select * from eod where name ~ 'AA'
now Explain indicate
Hi Chris,
the query below works for me -
select * from TABLENAME where 'TEXT' ~ pattern;
Thanks for helping!
Regards
chris smith writes:
> On 4/14/06, pgdb <[EMAIL PROTECTED]>wrote:
>>
>>
>> Hi Andreas,
>>
>> strange but I don't see html from my original
email received from the
On 4/14/06, pgdb <[EMAIL PROTECTED]> wrote:
>
>
> Hi Andreas,
>
> strange but I don't see html from my original email received from the
> mailing list, hope this reply is ok:)
>
> If I'm not wrong, the example you've provided is trying to return matching
> rows from multiple patterns and texts as i
Hi Andreas,
strange but I don't see html from my original email
received from the mailing list, hope this reply is ok:)
If I'm not wrong, the example you've provided is trying to
return matching rows from multiple patterns and texts as
inputs in the regex search.
The text in regular _expres
am 13.04.2006, um 12:47:38 + mailte pgdb folgendes:
>
>
>
>
> Hi,
>
> like to know how, if possible, for patterns
> as attribute in a table to be used in a regex search with a
> fixed string. The query should therefore return the rows that
> have matching patterns with the single text.
If your version does not support regexp_replace(), I have written a
similar function for easlier versions of postgresql using pl/pgsql
called regexp_replacex(). You can find it by searching google groups.
As the thread there points out, the function I wrote doesn't treat
NULLs properly as posted an
MaRCeLO PeReiRA <[EMAIL PROTECTED]> writes:
> Is there a way to execute a regex, inside a SELECT
> statement, to substitute things?
There's a regex_replace() function in recent PG versions. Or you could
write a function in plperl or pltcl to use the regex capabilities of
those languages.
Hi Tom. I misread the manual. I thought I could not do array_upper on
multidimensional array but it was specific concatenation functions.
Thank you for clarifying this.
Regards,
David
On Tuesday, July 5, 2005, at 01:22 PM, Tom Lane wrote:
David Pratt <[EMAIL PROTECTED]> writes:
Hi. I am
David Pratt <[EMAIL PROTECTED]> writes:
> Hi. I am using array_dims to give me dimensions of multidimensional
> array ie:
> [1:5][1:2]
> In my function I want to retreive the value of the second number from
> array_dims (5 in example above)
Why aren't you using array_upper()?
> This is what
At 4:26 PM -0400 8/21/2000, Tom Lane wrote:
>Michael Blakeley <[EMAIL PROTECTED]> writes:
>> Do I need to tell postgres to rebuild pg_language, perhaps?
>
>See the createlang utility script. PL languages aren't installed
>by default (due to possibly-overzealous concern about security).
Thanks -
Michael Blakeley <[EMAIL PROTECTED]> writes:
> Do I need to tell postgres to rebuild pg_language, perhaps?
See the createlang utility script. PL languages aren't installed
by default (due to possibly-overzealous concern about security).
regards, tom lane
At 1:30 AM -0400 8/21/2000, Tom Lane wrote:
>Michael Blakeley <[EMAIL PROTECTED]> writes:
>> Does postgresql support regex back-references?
>
>There's no such function at the SQL level, AFAIR.
>
>I'd recommend writing a function in either plperl or pltcl, according
>to your taste. Both offer pre
If you want to select all the employes whose last name begins with a C
you would use this regex '^C'. The ^ signifys the beginning of the
string and you dont need a * after the C because a * means _zero_ or
more matches, and that is the problem you were having
chris yambo
thoughtbubble productio
71 matches
Mail list logo
