Yea looks like Postgres has it right, well.. per POSIX standard anyway.
JavaScript also has it right, as does Python and .NET. Ruby is just weird.
On Thu, Jul 24, 2014 at 1:57 PM, Tom Lane wrote:
> Mike Christensen writes:
> > I'm curious why this query returns 0:
> > SELECT 'AAA' ~ '^A{,4}$
Mike Christensen writes:
> I'm curious why this query returns 0:
> SELECT 'AAA' ~ '^A{,4}$'
> Yet, this query returns 1:
> SELECT 'AAA' ~ '^A{0,4}$'
> Is this a bug with the regular expression engine?
Our regex documentation lists the following variants of bounds syntax:
{m}
{m,}
{
Yea seems right. I was testing the expression on Rubular (Which uses the
Ruby parser) and it worked. I guess Ruby allows this non-standard
expression with the missing lower bounds. Every reference I could find,
though, agrees only the upper bound is optional.
On Thu, Jul 24, 2014 at 1:42 PM, D
Mike Christensen-2 wrote
> I'm curious why this query returns 0:
>
> SELECT 'AAA' ~ '^A{,4}$'
>
> Yet, this query returns 1:
>
> SELECT 'AAA' ~ '^A{0,4}$'
>
> Is this a bug with the regular expression engine?
Apparently since "{,#}" is not a valid regexp expression the engine simply
interprets
I'm curious why this query returns 0:
SELECT 'AAA' ~ '^A{,4}$'
Yet, this query returns 1:
SELECT 'AAA' ~ '^A{0,4}$'
Is this a bug with the regular expression engine?
Ronan Dunklau writes:
> Unfortunately for my use case, "too much" quoting can lead to errors in
> postgresql.
AFAIR, the only stuff that's unsafe to insert a backslash before
is ASCII letters. This is documented in the fine print discussing
regular expressions, btw.
rega
On 24/02/2012 17:43, Heiko Wundram wrote:
> Am 24.02.2012 17:40, schrieb Ronan Dunklau:
>> On 24/02/2012 17:09, Heiko Wundram wrote:
>>> Use the corresponding function of your programming language/framework of
>>> choice. E.g. Python delivers this as re.escape().
>>
>> Thank you, but as I wrote in
Am 24.02.2012 17:40, schrieb Ronan Dunklau:
On 24/02/2012 17:09, Heiko Wundram wrote:
Use the corresponding function of your programming language/framework of
choice. E.g. Python delivers this as re.escape().
Thank you, but as I wrote in the original post, I don't know how
postgresql and pytho
On 24/02/2012 17:09, Heiko Wundram wrote:
> Am 24.02.2012 17:04, schrieb Ronan Dunklau:
>> On 24/02/2012 16:38, David Johnston wrote:
>>> You could (should?) write the escaping routine on the server side in
>>> a user-defined function:
>>>
>>> WHERE some_col ~ ('^' ||
>>> make_regexp_literal(user_s
Am 24.02.2012 17:04, schrieb Ronan Dunklau:
On 24/02/2012 16:38, David Johnston wrote:
You could (should?) write the escaping routine on the server side in a
user-defined function:
WHERE some_col ~ ('^' || make_regexp_literal(user_submitted_stringliteral) ||
'\d*$')
I totally agree, but I h
On 24/02/2012 16:38, David Johnston wrote:
> How about:
>
> WHERE some_col LIKE (user_submitted_input || '%') AND some_col ~ ('^.{' ||
> length_of_user_submitted_input || '}\d*$')
>
> I'd have some reservations regarding multi-byte characters however - but this
> avoids any escaping of the inp
-Original Message-
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Ronan Dunklau
Sent: Friday, February 24, 2012 6:34 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Regular expression character escape
Hello.
I'd like to perf
Hello.
I'd like to perform a query using user-submitted input in a regular
expression.
Something along the lines of:
select some_col
from some_table
where some_col ~ ('^' || user_submitted_input || '\d*$')
This query is looking for every value matching the user submitted input
with optional tra
On Fri, 2010-09-10 at 18:11 -0700, Nick wrote:
> I dont mind if the commas are at the beginning and end, im more
> concerned about "three,four" staying in one row because its surrounded
> by quotes. -Nick
It doesn't sound like a regex is the best solution here. Why not write a
function in a langua
I dont mind if the commas are at the beginning and end, im more
concerned about "three,four" staying in one row because its surrounded
by quotes. -Nick
On Sep 10, 6:03 pm, alvhe...@commandprompt.com (Alvaro Herrera) wrote:
> Excerpts from Nick's message of vie sep 10 20:36:24 -0400 2010:
>
> > Yes
Excerpts from Nick's message of vie sep 10 20:36:24 -0400 2010:
> Yes, that gets down to the root of my question... what is the
> expression that would properly split the values? -Nick
The only idea that comes to mind right now is to remove them before
processing the rest of the string, and put th
Yes, that gets down to the root of my question... what is the
expression that would properly split the values? -Nick
On Sep 10, 4:43 pm, brick...@gmail.com (bricklen) wrote:
> On Fri, Sep 10, 2010 at 3:43 PM, Nick wrote:
> > What would be the regexp_split_to_table pattern that splits a comma
> >
On Fri, Sep 10, 2010 at 3:43 PM, Nick wrote:
> What would be the regexp_split_to_table pattern that splits a comma
> separated string into a table? Im having trouble when a string
> contains commas or there are commas at the beginning or end
>
> String
> ',one,two,''three,four'',five,six,'
>
> Sho
What would be the regexp_split_to_table pattern that splits a comma
separated string into a table? Im having trouble when a string
contains commas or there are commas at the beginning or end
String
',one,two,''three,four'',five,six,'
Should return
,one
two
three,four
five
six,
--
Sent via pgsql
I have discovered the reasons of my problem.
Firstly I made a mistake at the type declaration of the column "value". It was
"character(3)". So that missing characters were filled with spaces and the
regular expression in case of less than three digits couldn't match at the end
("$"). I changed
On 09/09/10 11:55, Ungermann Carsten wrote:
Dear postgres list,
I need to verify the value of a column by a regular expression in an
if-statement before insert or update. It should be a one to three digit
value.
'^[0-9]{1,3}$' -- don't work
Works here.
CREATE TEMP TABLE tt (t text);
INSERT
I tried it once more, now. There is no change in the behavior. I use the
$$-notation to avoid double "\" and double "'". Also there is no syntax error
in this statement.
The constraint is a bad option because I have to log errors when I import a
csv-file.
Thanks and regards
Carsten Ungermann
On 9 September 2010 12:55, Ungermann Carsten <
carsten.ungerm...@ib-ungermann.de> wrote:
> Dear postgres list,
>
> I need to verify the value of a column by a regular expression in an
> if-statement before insert or update. It should be a one to three digit
> value. I wrote a trigger and a trigger
Dear postgres list,
I need to verify the value of a column by a regular expression in an
if-statement before insert or update. It should be a one to three digit value.
I wrote a trigger and a trigger function. In the trigger function I want to use
the following if-statement to verify the value:
On Tue, May 26, 2009 at 11:04 PM, Nick wrote:
> I wont go into details about why im using this field as an array but
> how would I select all the rows that have the first name 'Tom' out of
> the 'names' field?
>
> CREATE TABLE test (
> id integer,
> names character varying[]
> );
> INSERT IN
I wont go into details about why im using this field as an array but
how would I select all the rows that have the first name 'Tom' out of
the 'names' field?
CREATE TABLE test (
id integer,
names character varying[]
);
INSERT INTO test VALUES (1, '{"''Josh Berkus''","''Peter
Eisentraut''",
"Vyacheslav Kalinin" <[EMAIL PROTECTED]> writes:
> Case insensitive pattern matching gives strange results for non-ascii
> character (such as UTF-8 encoded cyrillic letters):
Yeah, the regex locale support doesn't work well in multibyte character
sets --- it basically will not recognize that non-A
Hello,
Case insensitive pattern matching gives strange results for non-ascii
character (such as UTF-8 encoded cyrillic letters):
test=# select 'б' ~* 'Б' ;
?column?
--
f
(1 row)
( 'б' and 'Б' are lower and upper case variants of cyrillic 'B')
at the same time:
test=# select 'б' ilike 'Б'
Paul Mendoza wrote:
> Sent: Saturday, September 08, 2007 12:53 AM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] Regular expression on a string problem.
>
> Here is a problem I'm having with a function I've created. It
> should be returning a varchar value no
Paul Mendoza wrote:
Here is a problem I'm having with a function I've created. It should be
returning a varchar value no matter what the input is but I'm getting a null
value back. Then it says in the error that I'm using "nonstandard use of
escape in a string literal at character 9". What do you
Here is a problem I'm having with a function I've created. It should be
returning a varchar value no matter what the input is but I'm getting a null
value back. Then it says in the error that I'm using "nonstandard use of
escape in a string literal at character 9". What do you think this should be
On Apr 21, 2007, at 11:01 , Richard Doust wrote:
select price from shipping_prices where shipFromZip = '23773' and
shipToZip ~ '87927'
because shipToZip is defined as a regular expression, I'd match a
row where shipToZip held the value '879[0-9]{2,2}' or '87[0-9]*'.
Wouldn't that be cool
Richard Doust <[EMAIL PROTECTED]> writes:
> What I'd like to be able to do is to define a field in the database
> as a regular expression so that when I select, i.e.,
> select price from shipping_prices where shipFromZip = '23773' and
> shipToZip ~ '87927'
> because shipToZip is defined as a
Hi.
I appreciate that I can query the database and find records that
match a regular expression.
What I'd like to be able to do is to define a field in the database
as a regular expression so that when I select, i.e.,
select price from shipping_prices where shipFromZip = '23773' and
shipTo
Ron Peterson <[EMAIL PROTECTED]> writes:
>> Sorry, I don't believe 8.1 took it either.
> Hmm, my test table fails in 8.1.4 also, but my actual use case works in
> 8.1.4 but not 8.2.
You haven't shown us exactly what that C function is doing, but my
interpretation of that is that 8.1 failed to che
On Mon, Jan 01, 2007 at 11:30:00PM -0500, Tom Lane wrote:
> Ron Peterson <[EMAIL PROTECTED]> writes:
> > I believe there's been a change in PostgreSQL's regular expression
> > handling w/ 8.2.
>
> Compared to what? A repeat count of 256 has been an error at least
> since 7.4, and is documented as
Ron Peterson <[EMAIL PROTECTED]> writes:
> I believe there's been a change in PostgreSQL's regular expression
> handling w/ 8.2.
Compared to what? A repeat count of 256 has been an error at least
since 7.4, and is documented as such:
: The numbers m and n within a bound are unsigned decimal inte
I believe there's been a change in PostgreSQL's regular expression
handling w/ 8.2.
CREATE TABLE testb (
name
TEXT
--CHECK( name ~ '^[a-f0-9]{1,256}$' )
CHECK( name ~ '^[a-f0-9]{1,255}$' )
);
If I swap the two check statements above, I can no longer insert data.
The operation errors
2006/12/18, Jeff Davis <[EMAIL PROTECTED]>:
On Fri, 2006-12-15 at 17:21 -0200, Clodoaldo wrote:
> -- drop table test_table;
> create table test_table (tname varchar, value integer);
> insert into test_table values ('[ab]x', 1);
> insert into test_table values ('[ab]y', 2);
> insert into test_tabl
On Fri, 2006-12-15 at 17:21 -0200, Clodoaldo wrote:
> -- drop table test_table;
> create table test_table (tname varchar, value integer);
> insert into test_table values ('[ab]x', 1);
> insert into test_table values ('[ab]y', 2);
> insert into test_table values ('[Ab]z', 3);
> insert into test_tabl
Regular expression and Group By
There is a varchar column which I need to group by an "uppered"
substring inside '[]' like in 'xxx[substring]yyy'. All the other lines
should not be changed.
I can do it using a union. I would like to reduce it to a single
query, but after much thought I can't. So
Hi,
Am Saturday 03 December 2005 12:48 schrieb Terry Lee Tucker:
| I have a situation where I need to extract a couple pieces of
| information from a string. The string, if entered perfectly by the user,
| would look someting like this: DUN: 006235835 SID: KT-3616*
|
| I need to extract the 006
On 12/03/2005 05:48:59 AM, Terry Lee Tucker wrote:
RE Gurus:
I have a situation where I need to extract a couple pieces of
information from
a string. The string, if entered perfectly by the user, would look
someting
like this: DUN: 006235835 SID: KT-3616*
I need to extract the 006235835 into
RE Gurus:
I have a situation where I need to extract a couple pieces of information from
a string. The string, if entered perfectly by the user, would look someting
like this: DUN: 006235835 SID: KT-3616*
I need to extract the 006235835 into one variable and the KT-3616 into
another. Both "num
On Thu, Apr 21, 2005 at 09:03:43AM -0400, David Gagnon wrote:
>
> Just want to share the solution I got to solve my problem. I wanted to
> be eable to search a string (say X) (non case sensitive) without having
> meta-character involved. The X string come directy from the web so any
> [%]* may
David Gagnon wrote:
Maybe there is a simple way to to this but I want find string X in
different column. The search must not be case sensitive.
So that searching "aBc" in "abcDef" return true. I don't want
META-CHaracter. Or at least I don't want meta-character to cause
errors (i.e.: N
Thanks for your help!
Just want to share the solution I got to solve my problem. I wanted to
be eable to search a string (say X) (non case sensitive) without having
meta-character involved. The X string come directy from the web so any
[%]* may cause error in regular expression (because they f
On Wed, Apr 20, 2005 at 11:28:28AM -0400, David Gagnon wrote:
>
> I have a web interface with offers a search field. This search field
> will look for the string X in 12 different columns. If the string is
> found anywhere I return the row.
>
> The problem is that the user is eable to put spa
On Wed, 2005-04-20 at 13:05, David Gagnon wrote:
> Hi Scott,
>
>
> >I would generally scrub the input before it go to postgresql. Basically
> >do a simple string_replace type function that replaces anything that
> >ISN'T alphanum with nothing.
> >
> >
> >
> If I change the original string the
Hi Scott,
I would generally scrub the input before it go to postgresql. Basically
do a simple string_replace type function that replaces anything that
ISN'T alphanum with nothing.
If I change the original string the user may not get what he expects as
result. abc[d] is not the samething tha
On Wed, 2005-04-20 at 12:36, David Gagnon wrote:
> Michael Fuhr wrote:
>
> >On Wed, Apr 20, 2005 at 11:28:28AM -0400, David Gagnon wrote:
> >
> >
> >>I have a web interface with offers a search field. This search field
> >>will look for the string X in 12 different columns. If the string is
Michael Fuhr wrote:
On Wed, Apr 20, 2005 at 11:28:28AM -0400, David Gagnon wrote:
I have a web interface with offers a search field. This search field
will look for the string X in 12 different columns. If the string is
found anywhere I return the row.
The problem is that the user is eable
Hi all,
I have a web interface with offers a search field. This search field
will look for the string X in 12 different columns. If the string is
found anywhere I return the row.
The problem is that the user is eable to put spacial character like : [*
This create invalid regular expression an
Hello,
You can use function translate
testdb011=# select
translate('XY1X234X','qwertyuioplkjhgfdsazxcvbnmQWERTYUIOPLKJHGFDSAZXCVBNM','');
translate
---
1234
(1 row)
Regards
Pavel Stehule
---(end of broadcast)---
TIP 9: the planner wil
My database table holds phone numbers that may contain characters other than
digits (that's not a problem in itself).
I want to be able to apply a regular expression (to ignore all characters except digits) to the attribute 'phone' first and then for the ILIKE to compare
the result to $telephone.
On Thu, Oct 07, 2004 at 04:07:08PM -0500, David Bitner wrote:
> I am trying to create a PL/PGSQL function that can parse a street address
> into the component parts (i.e. "200 W 54th Street" into num->200 dir->W
> street->54th type->ST).
>
> What I would like is to be able to use regular expressio
I am trying to create a PL/PGSQL function that can parse a street address
into the component parts (i.e. "200 W 54th Street" into num->200 dir->W
street->54th type->ST).
What I would like is to be able to use regular expressions within PL/PGSQL
to accomplish this using mapping tables for the diffe
Steve Heaven <[EMAIL PROTECTED]> writes:
> Does the regular expression parser have anything equivalent to Perl's \w
> word boundary metacharacter?
src/backend/regex/re_format.7 contains the whole scoop (for some reason
this page doesn't seem to get installed with the rest of the
documentation).
Title: RE: [GENERAL] Regular expression question
Yes, that's right :-0 Sorry!
-Original Message-
From: Steve Heaven [mailto:[EMAIL PROTECTED]]
Sent: 11 December 2000 15:09
To: Michael Ansley; [EMAIL PROTECTED]
Subject: RE: [GENERAL] Regular expression question
At 14:58 11/
Does the regular expression parser have anything equivalent to Perl's \w
word boundary metacharacter?
I want to select tuples where a text field contains a certail whole word.
Using fieldname ~* 'searchword' wont work because it picks up the
searchword emdedded in other words. Using ~*' searchwor
60 matches
Mail list logo
