Re: [GENERAL] Replacing Ordinal Suffixes

2014-03-01 Thread Alvaro Herrera 
George Weaver wrote:

 Hi Ian,
 
 I just got that as well - awesome!

http://xkcd.com/1313/

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training  Services


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Replacing Ordinal Suffixes

2014-03-01 Thread George Weaver 

Hi All,


From: James Cloos



The E'' syntax eats your backslashes.  For that version, try just:



SELECT REGEXP_REPLACE(LOWER('300 North 126th Street'),'(\d)(st|nd|rd|th)',
'\1', 'g');


Actually, I found that the double backslashes are required whether the E is 
used or not:


development=# select regexp_replace('300 North 126th Street', 
'(\d+)(?:st|nd|rd|th)', '\1', 'gi');

WARNING:  nonstandard use of escape in a string literal
LINE 1: select regexp_replace('300 North 126th Street', '(\d+)(?:st|...
   ^
HINT:  Use the escape string syntax for escapes, e.g., E'\r\n'.
WARNING:  nonstandard use of escape in a string literal
LINE 1: ...'300 North 126th Street', '(\d+)(?:st|nd|rd|th)', '\1', 'gi'...
^
HINT:  Use the escape string syntax for escapes, e.g., E'\r\n'.
regexp_replace

300 North 126th Street
(1 row)

development=# select regexp_replace('300 North 126th Street', 
'(\\d+)(?:st|nd|rd|th)', '\\1', 'gi');

WARNING:  nonstandard use of \\ in a string literal
LINE 1: select regexp_replace('300 North 126th Street', '(\\d+)(?:st...
   ^
HINT:  Use the escape string syntax for backslashes, e.g., E'\\'.
WARNING:  nonstandard use of \\ in a string literal
LINE 1: ...300 North 126th Street', '(\\d+)(?:st|nd|rd|th)', '\\1', 'gi...
^
HINT:  Use the escape string syntax for backslashes, e.g., E'\\'.
   regexp_replace
--
300 North 126 Street
(1 row)

development=# select regexp_replace('300 North 126th Street', 
E'(\\d+)(?:st|nd|rd|th)', E'\\1', 'gi');

   regexp_replace
--
300 North 126 Street

I appreciate the help of everyone who responded!

Cheers,
George 




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Replacing Ordinal Suffixes

2014-03-01 Thread Tom Lane 
George Weaver gwea...@shaw.ca writes:
 Actually, I found that the double backslashes are required whether the E is 
 used or not:

You must be using a relatively old PG version then.  Default behavior
since around 9.1 has been that backslashes aren't special except
in E'' strings.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Replacing Ordinal Suffixes

2014-03-01 Thread George Weaver 


Actually, I found that the double backslashes are required whether the E 
is

used or not:



You must be using a relatively old PG version then.  Default behavior
since around 9.1 has been that backslashes aren't special except
in E'' strings.


Hmm.

development=# select version();
  version

PostgreSQL 9.1.9, compiled by Visual C++ build 1500, 32-bit
(1 row)

regards, tom lane 




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Replacing Ordinal Suffixes

2014-03-01 Thread Steve Atkins 

On Mar 1, 2014, at 11:45 AM, George Weaver gwea...@shaw.ca wrote:

 
 Actually, I found that the double backslashes are required whether the E is
 used or not:
 
 You must be using a relatively old PG version then.  Default behavior
 since around 9.1 has been that backslashes aren't special except
 in E'' strings.
 
 Hmm.
 
 development=# select version();
  version
 
 PostgreSQL 9.1.9, compiled by Visual C++ build 1500, 32-bit
 (1 row)

steve=# select version();

 version
--
 PostgreSQL 9.1.4 on x86_64-apple-darwin12.0.0, compiled by 
i686-apple-darwin11-llvm-gcc-4.2 (GCC) 4.2.1 (Based on Apple Inc. build 5658) 
(LLVM build 2336.11.00), 64-bit
(1 row)

steve=# select regexp_replace('300 North 126th Street', '(\d+)(?:st|nd|rd|th)', 
'\1', 'gi');
regexp_replace
--
 300 North 126 Street
(1 row)


I suspect you have standard_conforming_strings set to off (it defaults to on in 
9.1), possibly for backwards compatibility to support an app you’re using 
that’s not been updated, possibly accidentally.

Cheers,
  Steve



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Replacing Ordinal Suffixes

2014-03-01 Thread George Weaver 


- Original Message - 
From: Steve Atkins


snip


I suspect you have standard_conforming_strings set to off (it defaults to
on in 9.1), possibly for backwards compatibility to support an app you’re
using that’s not been updated, possibly accidentally.


You're right - it was off (now On).  Though for the life of me I can't say 
why...


On the production installations I support its On.

Thanks!


Cheers,

 Steve




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Replacing Ordinal Suffixes

2014-02-28 Thread George Weaver 
Hi list,

I'm stumped.

I am trying to use Regexp_Replace to replace ordinal suffixes in addresses (eg 
have '126th' want '126') for comparison purposes.  So far no luck.

I have found that 

SELECT REGEXP_REPLACE(LOWER('300 North 126th Street'), '(?!/D)(st|nd|rd|th)', 
'', 'g');
  regexp_replace
--
 300 nor 126 reet

but 

SELECT REGEXP_REPLACE(LOWER('300 North 126th Street'), '(?=/D)(st|nd|rd|th)', 
'', 'g');
 regexp_replace

 300 north 126th street

I'm a novice with regular expressions and google hasn't helped much.

Any suggestions?

Thanks,
George

Re: [GENERAL] Replacing Ordinal Suffixes

2014-02-28 Thread Paul Jungwirth 
Try this:

SELECT REGEXP_REPLACE(LOWER('300 North 126th Street'),
'(\d)(st|nd|rd|th)', '\1', 'g');

Note that matching a number is \d not /D: backslash, not forward
slash, and lowercase d not uppercase. \d means a digit, \D means
anything except a digit.

Also, I don't think Postgres supports positive lookbehind expressions
(which are actually (?=foo), not (?!foo)), but you can get the same
effect by capturing the number with (\d) and then outputting it again
with the \1.

Paul



On Fri, Feb 28, 2014 at 2:04 PM, George Weaver gwea...@shaw.ca wrote:
 Hi list,

 I'm stumped.

 I am trying to use Regexp_Replace to replace ordinal suffixes in addresses
 (eg have '126th' want '126') for comparison purposes.  So far no luck.

 I have found that

 SELECT REGEXP_REPLACE(LOWER('300 North 126th Street'),
 '(?!/D)(st|nd|rd|th)', '', 'g');
   regexp_replace
 --
  300 nor 126 reet

 but

 SELECT REGEXP_REPLACE(LOWER('300 North 126th Street'),
 '(?=/D)(st|nd|rd|th)', '', 'g');
  regexp_replace
 
  300 north 126th street

 I'm a novice with regular expressions and google hasn't helped much.

 Any suggestions?

 Thanks,
 George



-- 
_
Pulchritudo splendor veritatis.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Replacing Ordinal Suffixes

2014-02-28 Thread Steve Atkins 

On Feb 28, 2014, at 2:04 PM, George Weaver gwea...@shaw.ca wrote:

 Hi list,
  
 I'm stumped.
  
 I am trying to use Regexp_Replace to replace ordinal suffixes in addresses 
 (eg have '126th' want '126') for comparison purposes.  So far no luck.
  
 I have found that
  
 SELECT REGEXP_REPLACE(LOWER('300 North 126th Street'), '(?!/D)(st|nd|rd|th)', 
 '', 'g');
   regexp_replace
 --
  300 nor 126 reet
  
 but
  
 SELECT REGEXP_REPLACE(LOWER('300 North 126th Street'), '(?=/D)(st|nd|rd|th)', 
 '', 'g');
  regexp_replace
 
  300 north 126th street
  
 I'm a novice with regular expressions and google hasn't helped much.
  
 Any suggestions?

Maybe this?

select regexp_replace('300 North 126th Street', '(\d+)(?:st|nd|rd|th)', '\1', 
'gi');

Cheers,
  Steve



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Replacing Ordinal Suffixes

2014-02-28 Thread Bret Stern 
This is a kick *ss forum. I must say.


On Fri, 2014-02-28 at 14:17 -0800, Steve Atkins wrote:
 On Feb 28, 2014, at 2:04 PM, George Weaver gwea...@shaw.ca wrote:
 
  Hi list,
   
  I'm stumped.
   
  I am trying to use Regexp_Replace to replace ordinal suffixes in addresses 
  (eg have '126th' want '126') for comparison purposes.  So far no luck.
   
  I have found that
   
  SELECT REGEXP_REPLACE(LOWER('300 North 126th Street'), 
  '(?!/D)(st|nd|rd|th)', '', 'g');
regexp_replace
  --
   300 nor 126 reet
   
  but
   
  SELECT REGEXP_REPLACE(LOWER('300 North 126th Street'), 
  '(?=/D)(st|nd|rd|th)', '', 'g');
   regexp_replace
  
   300 north 126th street
   
  I'm a novice with regular expressions and google hasn't helped much.
   
  Any suggestions?
 
 Maybe this?
 
 select regexp_replace('300 North 126th Street', '(\d+)(?:st|nd|rd|th)', '\1', 
 'gi');
 
 Cheers,
   Steve
 
 
 




-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Replacing Ordinal Suffixes

2014-02-28 Thread George Weaver 

From: Paul Jungwirth


Try this:



SELECT REGEXP_REPLACE(LOWER('300 North 126th Street'),

'(\d)(st|nd|rd|th)', '\1', 'g');

Hi Paul,

No luck...

SELECT REGEXP_REPLACE(LOWER('300 North 126th Street'), E'(\d)(st|nd|rd|th)', 
E'\1', 'g');

regexp_replace

300 north 126th street
(1 row)


Note that matching a number is \d not /D: backslash, not forward
slash, and lowercase d not uppercase. \d means a digit, \D means
anything except a digit.



Also, I don't think Postgres supports positive lookbehind expressions
(which are actually (?=foo), not (?!foo)), but you can get the same
effect by capturing the number with (\d) and then outputting it again
with the \1.



Paul




On Fri, Feb 28, 2014 at 2:04 PM, George Weaver gwea...@shaw.ca wrote:

Hi list,

I'm stumped.

I am trying to use Regexp_Replace to replace ordinal suffixes in addresses
(eg have '126th' want '126') for comparison purposes.  So far no luck.

I have found that

SELECT REGEXP_REPLACE(LOWER('300 North 126th Street'),
'(?!/D)(st|nd|rd|th)', '', 'g');
  regexp_replace
--
 300 nor 126 reet

but

SELECT REGEXP_REPLACE(LOWER('300 North 126th Street'),
'(?=/D)(st|nd|rd|th)', '', 'g');
 regexp_replace

 300 north 126th street

I'm a novice with regular expressions and google hasn't helped much.

Any suggestions?

Thanks,
George




--
_
Pulchritudo splendor veritatis.



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Replacing Ordinal Suffixes

2014-02-28 Thread George Weaver 
From: Steve Atkins

On Feb 28, 2014, at 2:04 PM, George Weaver gwea...@shaw.ca wrote:

 Hi list,

 I'm stumped.

 I am trying to use Regexp_Replace to replace ordinal suffixes in addresses 
 (eg have '126th' want '126') for comparison purposes.  So far no luck.

 I have found that

 SELECT REGEXP_REPLACE(LOWER('300 North 126th Street'), 
 '(?!/D)(st|nd|rd|th)', '', 'g');
   regexp_replace
 --
  300 nor 126 reet

 but

 SELECT REGEXP_REPLACE(LOWER('300 North 126th Street'), 
 '(?=/D)(st|nd|rd|th)', '', 'g');
  regexp_replace
 
  300 north 126th street

 I'm a novice with regular expressions and google hasn't helped much.

 Any suggestions?

Maybe this?

select regexp_replace('300 North 126th Street', '(\d+)(?:st|nd|rd|th)', 
'\1', 'gi');

Hi Steve,

Thanks, but no luck:

select regexp_replace('300 North 126th Street', E'(\d+)(?:st|nd|rd|th)', 
E'\1', 'gi');
 regexp_replace

 300 North 126th Street

George

Cheers,
  Steve




-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Replacing Ordinal Suffixes

2014-02-28 Thread Steve Atkins 

On Feb 28, 2014, at 2:43 PM, George Weaver gwea...@shaw.ca wrote:

 From: Steve Atkins
 
 Maybe this?
 
 select regexp_replace('300 North 126th Street', '(\d+)(?:st|nd|rd|th)', 
 '\1', 'gi');
 
 Hi Steve,
 
 Thanks, but no luck:
 
 select regexp_replace('300 North 126th Street', E'(\d+)(?:st|nd|rd|th)', 
 E'\1', 'gi');
  regexp_replace
 
  300 North 126th Street
 
 George

Those E’s you added completely change the meaning. If you want to
use E-style literals (and you probably don’t) you’ll need to double the
backslashes in all the strings.

Cheers,
  Steve



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Replacing Ordinal Suffixes

2014-02-28 Thread George Weaver 


- Original Message - 
From: Steve Atkins



On Feb 28, 2014, at 2:43 PM, George Weaver gwea...@shaw.ca wrote:

Maybe this?

select regexp_replace('300 North 126th Street', '(\d+)(?:st|nd|rd|th)',
'\1', 'gi');

Hi Steve,

Thanks, but no luck:

select regexp_replace('300 North 126th Street', E'(\d+)(?:st|nd|rd|th)',
E'\1', 'gi');
 regexp_replace

 300 North 126th Street

George



Those E’s you added completely change the meaning. If you want to
use E-style literals (and you probably don’t) you’ll need to double the
backslashes in all the strings.


Hi Steve,

Without the E's:

development=# select regexp_replace('300 North 126th Street', 
'(\d+)(?:st|nd|rd|th)', '\1', 'gi');

WARNING:  nonstandard use of escape in a string literal
LINE 1: select regexp_replace('300 North 126th Street', '(\d+)(?:st|...
   ^
HINT:  Use the escape string syntax for escapes, e.g., E'\r\n'.
WARNING:  nonstandard use of escape in a string literal
LINE 1: ...'300 North 126th Street', '(\d+)(?:st|nd|rd|th)', '\1', 'gi'...
^
HINT:  Use the escape string syntax for escapes, e.g., E'\r\n'.
regexp_replace

300 North 126th Street
(1 row)

Frustrating...


Cheers,

 Steve



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Replacing Ordinal Suffixes

2014-02-28 Thread George Weaver 
- Original Message - 
From: Steve Atkins

To: pgsql-general
Sent: Friday, February 28, 2014 4:17 PM
Subject: Re: [GENERAL] Replacing Ordinal Suffixes



On Feb 28, 2014, at 2:04 PM, George Weaver gwea...@shaw.ca wrote:


Hi list,

I'm stumped.

I am trying to use Regexp_Replace to replace ordinal suffixes in addresses 
(eg have '126th' want '126') for comparison purposes.  So far no luck.


I have found that

SELECT REGEXP_REPLACE(LOWER('300 North 126th Street'), 
'(?!/D)(st|nd|rd|th)', '', 'g');

  regexp_replace
--
 300 nor 126 reet

but

SELECT REGEXP_REPLACE(LOWER('300 North 126th Street'), 
'(?=/D)(st|nd|rd|th)', '', 'g');

 regexp_replace

 300 north 126th street

I'm a novice with regular expressions and google hasn't helped much.

Any suggestions?



Maybe this?


select regexp_replace('300 North 126th Street', '(\d+)(?:st|nd|rd|th)', 
'\1', 'gi');


Hi Steve,

Thanks, but no luck:

select regexp_replace('300 North 126th Street', E'(\d+)(?:st|nd|rd|th)', 
E'\1', 'gi');

regexp_replace

300 North 126th Street

George

Cheers,
 Steve




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general 




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Replacing Ordinal Suffixes

2014-02-28 Thread Ian Lawrence Barwick 
2014-03-01 8:16 GMT+09:00 George Weaver gwea...@shaw.ca:

 - Original Message - From: Steve Atkins


 On Feb 28, 2014, at 2:43 PM, George Weaver gwea...@shaw.ca wrote:

 Maybe this?

 select regexp_replace('300 North 126th Street', '(\d+)(?:st|nd|rd|th)',
 '\1', 'gi');

 Hi Steve,

 Thanks, but no luck:

 select regexp_replace('300 North 126th Street', E'(\d+)(?:st|nd|rd|th)',
 E'\1', 'gi');
  regexp_replace
 
  300 North 126th Street

 George


 Those E’s you added completely change the meaning. If you want to
 use E-style literals (and you probably don’t) you’ll need to double the
 backslashes in all the strings.


 Hi Steve,

 Without the E's:

 development=# select regexp_replace('300 North 126th Street',
 '(\d+)(?:st|nd|rd|th)', '\1', 'gi');
 WARNING:  nonstandard use of escape in a string literal
 LINE 1: select regexp_replace('300 North 126th Street', '(\d+)(?:st|...
^
 HINT:  Use the escape string syntax for escapes, e.g., E'\r\n'.
 WARNING:  nonstandard use of escape in a string literal
 LINE 1: ...'300 North 126th Street', '(\d+)(?:st|nd|rd|th)', '\1', 'gi'...
 ^
 HINT:  Use the escape string syntax for escapes, e.g., E'\r\n'.

 regexp_replace
 
 300 North 126th Street
 (1 row)

 Frustrating...

Per Steve Atkin's note about double backslashes:

postgres= select regexp_replace('300 North 126th Street',
E'(\\d+)(?:st|nd|rd|th)', E'\\1', 'gi');
regexp_replace
--
 300 North 126 Street
(1 row)

Regards

Ian Barwick


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Replacing Ordinal Suffixes

2014-02-28 Thread George Weaver 

From: Ian Lawrence Barwick



- Original Message - From: Steve Atkins




On Feb 28, 2014, at 2:43 PM, George Weaver gwea...@shaw.ca wrote:

Maybe this?

select regexp_replace('300 North 126th Street', '(\d+)(?:st|nd|rd|th)',
'\1', 'gi');

Hi Steve,

Thanks, but no luck:

select regexp_replace('300 North 126th Street', E'(\d+)(?:st|nd|rd|th)',
E'\1', 'gi');
 regexp_replace

 300 North 126th Street

George




Those E’s you added completely change the meaning. If you want to
use E-style literals (and you probably don’t) you’ll need to double the
backslashes in all the strings.



Hi Steve,

Without the E's:

development=# select regexp_replace('300 North 126th Street',
'(\d+)(?:st|nd|rd|th)', '\1', 'gi');
WARNING:  nonstandard use of escape in a string literal
LINE 1: select regexp_replace('300 North 126th Street', '(\d+)(?:st|...
   ^
HINT:  Use the escape string syntax for escapes, e.g., E'\r\n'.
WARNING:  nonstandard use of escape in a string literal
LINE 1: ...'300 North 126th Street', '(\d+)(?:st|nd|rd|th)', '\1', 'gi'...
^
HINT:  Use the escape string syntax for escapes, e.g., E'\r\n'.

regexp_replace

300 North 126th Street
(1 row)

Frustrating...


Per Steve Atkin's note about double backslashes:

   postgres= select regexp_replace('300 North 126th Street',
E'(\\d+)(?:st|nd|rd|th)', E'\\1', 'gi');
   regexp_replace
   --
300 North 126 Street
   (1 row)

Hi Ian,

I just got that as well - awesome!

Regards

Ian Barwick 




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Replacing Ordinal Suffixes

2014-02-28 Thread James Cloos 
 GW == George Weaver gwea...@shaw.ca writes:

GW SELECT REGEXP_REPLACE(LOWER('300 North 126th Street'),
GW E'(\d)(st|nd|rd|th)', E'\1', 'g');
GW regexp_replace
GW 
GW 300 north 126th street
GW (1 row)

The E'' syntax eats your backslashes.  For that version, try just:

SELECT REGEXP_REPLACE(LOWER('300 North 126th Street'),'(\d)(st|nd|rd|th)', 
'\1', 'g');

-JimC
--
James Cloos cl...@jhcloos.com OpenPGP: 1024D/ED7DAEA6


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general