Re: [SQL] SELECT substring with regex

T E Schmitz wrote: > valid entries would be: > "28mm F2.8" (prime lens) > "30-70mm F4" (zoom lens) > "30-70mm F2.8" (zoom lens) > "30-100mm F4.5-5.6" (zoom lens with variable speed) I think you already got all the help you needed, but I want to point out that the Nikon 18-70mm that was used to ta

Re: [SQL] SELECT substring with regex

Emils wrote: 2006/7/7, T E Schmitz <[EMAIL PROTECTED]>: valid entries would be: "28mm F2.8" (prime lens) "30-70mm F4" (zoom lens) "30-70mm F2.8" (zoom lens) "30-100mm F4.5-5.6" (zoom lens with variable speed) If these are the cases, wouldn't the regex be simply: "^[\d\-]+mm" for BASE "^[\

Re: [SQL] SELECT substring with regex

2006/7/7, T E Schmitz <[EMAIL PROTECTED]>: valid entries would be: "28mm F2.8" (prime lens) "30-70mm F4" (zoom lens) "30-70mm F2.8" (zoom lens) "30-100mm F4.5-5.6" (zoom lens with variable speed) If these are the cases, wouldn't the regex be simply: "^[\d\-]+mm" for BASE "^[\d\-]+mm (.+)$" f

Re: [SQL] SELECT substring with regex

On 7/8/06, T E Schmitz <[EMAIL PROTECTED]> wrote: Is regexp_replace a new feature? I am running v 7.4. Given the patch history: http://archives.postgresql.org/pgsql-patches/2004-07/msg00471.php http://archives.postgresql.org/pgsql-patches/2005-06/msg00515.php http://archives.postgresql.org/pgs

Re: [SQL] SELECT substring with regex

Aaron Bono wrote: On 7/7/06, *T E Schmitz* <[EMAIL PROTECTED] > wrote: I am trying to come up with a semi-automatic solution to tidy up some data. If it's got to be done manually via the GUI it would mean a lot of dummy work [for the customer]. I would r

Re: [SQL] SELECT substring with regex

Rodrigo De Leon wrote: On 7/7/06, T E Schmitz <[EMAIL PROTECTED]> wrote: Sorry, but that would also capture something like 10-30-59mm The pattern describes either a single length (120 millimeters) or a range (30 to 70 millimetres), hence: \\d+(-\\d+)?mm The ? quantifier refers to the combina

Re: [SQL] SELECT substring with regex

On 7/7/06, T E Schmitz <[EMAIL PROTECTED]> wrote: Sorry, but that would also capture something like 10-30-59mm The pattern describes either a single length (120 millimeters) or a range (30 to 70 millimetres), hence: \\d+(-\\d+)?mm The ? quantifier refers to the combination of '-' and digits an

Re: [SQL] SELECT substring with regex

On 7/7/06, T E Schmitz <[EMAIL PROTECTED]> wrote: I am trying to come up with a semi-automatic solution to tidy up somedata. If it's got to be done manually via the GUI it would mean a lot ofdummy work [for the customer].I would recommend you alter the GUI to NOT allow any more bad data to get in -

Re: [SQL] SELECT substring with regex

> valid entries would be: > "28mm F2.8" (prime lens) > "30-70mm F4" (zoom lens) > "30-70mm F2.8" (zoom lens) > "30-100mm F4.5-5.6" (zoom lens with variable speed) > > > In the WHERE clause I have specified all those NAMEs, which follow that > pattern but have some gubbins appended: > > WHERE NA

Re: [SQL] SELECT substring with regex

Erik Jones wrote: T E Schmitz wrote: Gary Stainburn wrote: On Friday 07 July 2006 14:51, T E Schmitz wrote: I would like to split the contents of a column using substring with a regular expression: The column contains something like "150mm LD AD Asp XR Macro" I want to split this into "150

Re: [SQL] SELECT substring with regex

use plperl -- Original Message --- From: T E Schmitz <[EMAIL PROTECTED]> To: pgsql-sql@postgresql.org Sent: Fri, 07 Jul 2006 20:23:50 +0100 Subject: Re: [SQL] SELECT substring with regex > Rodrigo De Leon wrote: > > On 7/7/06, T E Schmitz <[EMAIL PROTECTED]>

Re: [SQL] SELECT substring with regex

Aaron Bono wrote: On 7/7/06, *Rodrigo De Leon* <[EMAIL PROTECTED] > wrote: On 7/7/06, T E Schmitz <[EMAIL PROTECTED] > wrote: > But that takes me to the next problem: > > For the sake of the example I simplified the regu

Re: [SQL] SELECT substring with regex

On 7/7/06, Rodrigo De Leon <[EMAIL PROTECTED]> wrote: On 7/7/06, T E Schmitz <[EMAIL PROTECTED]> wrote:> But that takes me to the next problem:>> For the sake of the example I simplified the regular pattern. > In reality, BASE_NAME might be:>> 28mm> 28-70mm>> So the reg. expr. requires brackets:>>

Re: [SQL] SELECT substring with regex

Rodrigo De Leon wrote: On 7/7/06, T E Schmitz <[EMAIL PROTECTED]> wrote: But that takes me to the next problem: For the sake of the example I simplified the regular pattern. In reality, BASE_NAME might be: 28mm 28-70mm So the reg. expr. requires brackets: substring (NAME, '^(\\d+(-\\d+)?mm)

Re: [SQL] SELECT substring with regex

T E Schmitz wrote: Gary Stainburn wrote: On Friday 07 July 2006 14:51, T E Schmitz wrote: I would like to split the contents of a column using substring with a regular expression: The column contains something like "150mm LD AD Asp XR Macro" I want to split this into "150mm", "LD AD Asp XR Ma

Re: [SQL] SELECT substring with regex

On 7/7/06, T E Schmitz <[EMAIL PROTECTED]> wrote: But that takes me to the next problem: For the sake of the example I simplified the regular pattern. In reality, BASE_NAME might be: 28mm 28-70mm So the reg. expr. requires brackets: substring (NAME, '^(\\d+(-\\d+)?mm)' ) as BASE_NAME Actuall

Re: [SQL] SELECT substring with regex

Gary Stainburn wrote: On Friday 07 July 2006 14:51, T E Schmitz wrote: I would like to split the contents of a column using substring with a regular expression: The column contains something like "150mm LD AD Asp XR Macro" I want to split this into "150mm", "LD AD Asp XR Macro" select subs

Re: [SQL] SELECT substring with regex

On Friday 07 July 2006 14:51, T E Schmitz wrote: > I would like to split the contents of a column using substring with a > regular expression: > > SELECT > substring (NAME, '^\\d+mm') as BASE_NAME, > substring (NAME, ??? ) as SUFFIX > FROM MODEL > > The column contains something like > "150mm L

Re: [SQL] SELECT substring with regex

On 7/7/06, T E Schmitz <[EMAIL PROTECTED]> wrote: I would like to split the contents of a column using substring with a regular expression: SELECT substring (NAME, '^\\d+mm') as BASE_NAME, substring (NAME, ??? ) as SUFFIX FROM MODEL The column contains something like "150mm LD AD Asp XR Mac

[SQL] SELECT substring with regex

I would like to split the contents of a column using substring with a regular expression: SELECT substring (NAME, '^\\d+mm') as BASE_NAME, substring (NAME, ??? ) as SUFFIX FROM MODEL The column contains something like "150mm LD AD Asp XR Macro" I want to split this into "150mm", "LD AD Asp XR