pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSELECT substring with regex
20+ messages / 9 participants
[nested] [flat]
* SELECT substring with regex
@ 2006-07-07 13:51 T E Schmitz <mailreg@numerixtechnology.de>
0 siblings, 2 replies; 20+ messages in thread
From: T E Schmitz @ 2006-07-07 13:51 UTC (permalink / raw)
To: pgsql-sql
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 Macro"
How can I extract the bit following the matching substring?
--
Regards,
Tarlika Elisabeth Schmitz
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: SELECT substring with regex
@ 2006-07-07 15:55 Rodrigo De Leon <rdeleonp@gmail.com>
parent: T E Schmitz <mailreg@numerixtechnology.de>
1 sibling, 0 replies; 20+ messages in thread
From: Rodrigo De Leon @ 2006-07-07 15:55 UTC (permalink / raw)
To: pgsql-sql
On 7/7/06, T E Schmitz <mailreg@numerixtechnology.de> 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 Macro"
> I want to split this into
> "150mm", "LD AD Asp XR Macro"
>
> How can I extract the bit following the matching substring?
>
> --
>
>
> Regards,
>
> Tarlika Elisabeth Schmitz
I'm sure there's a cleaner, regexp based approach, but how about:
SELECT
substring (NAME, '^\\d+mm') AS BASE_NAME ,
substr(
NAME
, char_length(
substring (NAME, '^\\d+mm')
) + 2
) AS SUFFIX
FROM MODEL
Regards,
Rodrigo
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: SELECT substring with regex
@ 2006-07-07 15:59 Gary Stainburn <gary.stainburn@ringways.co.uk>
parent: T E Schmitz <mailreg@numerixtechnology.de>
1 sibling, 1 reply; 20+ messages in thread
From: Gary Stainburn @ 2006-07-07 15:59 UTC (permalink / raw)
To: pgsql-sql
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 LD AD Asp XR Macro"
> I want to split this into
> "150mm", "LD AD Asp XR Macro"
>
> How can I extract the bit following the matching substring?
select substring('150mm LD AD Asp XR Macro','^\\d+mm') as BASE_NAME,
substring('150mm LD AD Asp XR Macro','^\\d+mm (.*)$') as SUFFIX;
base_name | suffix
-----------+--------------------
150mm | LD AD Asp XR Macro
(1 row)
The brackets surround the required match
--
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: SELECT substring with regex
@ 2006-07-07 17:35 T E Schmitz <mailreg@numerixtechnology.de>
parent: Gary Stainburn <gary.stainburn@ringways.co.uk>
0 siblings, 2 replies; 20+ messages in thread
From: T E Schmitz @ 2006-07-07 17:35 UTC (permalink / raw)
To: pgsql-sql
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 substring('150mm LD AD Asp XR Macro','^\\d+mm') as BASE_NAME,
> substring('150mm LD AD Asp XR Macro','^\\d+mm (.*)$') as SUFFIX;
> base_name | suffix
> -----------+--------------------
> 150mm | LD AD Asp XR Macro
> (1 row)
>
> The brackets surround the required match
This is ingenious! I had been looking at chapter 9.6 Pattern Matching.
Am I missing something? I did not realize that the brackets indicate
the required match.
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
Actually, the pattern is more complex than that and I cannot see how I
can express it without brackets.
--
Regards/Gruß,
Tarlika Elisabeth Schmitz
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: SELECT substring with regex
@ 2006-07-07 18:37 Rodrigo De Leon <rdeleonp@gmail.com>
parent: T E Schmitz <mailreg@numerixtechnology.de>
1 sibling, 2 replies; 20+ messages in thread
From: Rodrigo De Leon @ 2006-07-07 18:37 UTC (permalink / raw)
To: pgsql-sql
On 7/7/06, T E Schmitz <mailreg@numerixtechnology.de> 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
>
> Actually, the pattern is more complex than that and I cannot see how I
> can express it without brackets.
Maybe:
select
substring ('150mm LD AD Asp XR Macro', '^[\\d-]*mm' ) as BASE_NAME
, substring('150mm LD AD Asp XR Macro','^[\\d-]*mm (.*)$') as SUFFIX;
select
substring ('28-70mm LD AD Asp XR Macro', '^[\\d-]*mm' ) as BASE_NAME
, substring('28-70mm LD AD Asp XR Macro','^[\\d-]*mm (.*)$') as SUFFIX;
etc...
Regards,
Rodrigo
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: SELECT substring with regex
@ 2006-07-07 18:58 Erik Jones <erik@myemma.com>
parent: T E Schmitz <mailreg@numerixtechnology.de>
1 sibling, 1 reply; 20+ messages in thread
From: Erik Jones @ 2006-07-07 18:58 UTC (permalink / raw)
To: mailreg@numerixtechnology.de; +Cc: pgsql-sql
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 Macro"
>>>
>>
>>
>> select substring('150mm LD AD Asp XR Macro','^\\d+mm') as BASE_NAME,
>> substring('150mm LD AD Asp XR Macro','^\\d+mm (.*)$') as SUFFIX;
>> base_name | suffix
>> -----------+--------------------
>> 150mm | LD AD Asp XR Macro
>> (1 row)
>>
>> The brackets surround the required match
>
>
> This is ingenious! I had been looking at chapter 9.6 Pattern Matching.
> Am I missing something? I did not realize that the brackets indicate
> the required match.
>
> 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
>
> Actually, the pattern is more complex than that and I cannot see how I
> can express it without brackets.
>
>
Will the mm always be the end of the base name?
substring(NAME, '^(.*?mm)') as BASE_NAME
That should match all the way up to the first mm. If there are actually
a set number of different units that it might end in, such as mm and cm,
you can do:
substring(NAME, '^(.*?(mm|cm))') as BASE_NAME
That will match everything up to the first mm or cm. Note that you
don't have to worry about the second set of brackets returning anything
as the regexp version of substring only returns what is matched by the
first parenthesised subexpression.
--
erik jones <erik@myemma.com>
software development
emma(r)
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: SELECT substring with regex
@ 2006-07-07 19:23 T E Schmitz <mailreg@numerixtechnology.de>
parent: Rodrigo De Leon <rdeleonp@gmail.com>
1 sibling, 2 replies; 20+ messages in thread
From: T E Schmitz @ 2006-07-07 19:23 UTC (permalink / raw)
To: pgsql-sql
Rodrigo De Leon wrote:
> On 7/7/06, T E Schmitz <mailreg@numerixtechnology.de> 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
>>
>> Actually, the pattern is more complex than that and I cannot see how I
>> can express it without brackets.
>
>
> Maybe:
>
> select
> substring ('28-70mm LD AD Asp XR Macro', '^[\\d-]*mm' ) as BASE_NAME
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 and has to
be bracketed.
If the brackets cannot be avoided in the expression, your original
suggestion might come in handy though:
SELECT
substring (NAME, '^\\d+(-\\d+)?mm') AS BASE_NAME ,
substr(
NAME
, char_length(
substring (NAME, '^\\d+(-\\d+)?mm')
) + 2
) AS SUFFIX
Still, I'd be interested to know whether there is a 'more elegant' solution.
--
Regards,
Tarlika Elisabeth Schmitz
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: SELECT substring with regex
@ 2006-07-07 19:45 Aaron Bono <postgresql@aranya.com>
parent: Rodrigo De Leon <rdeleonp@gmail.com>
1 sibling, 1 reply; 20+ messages in thread
From: Aaron Bono @ 2006-07-07 19:45 UTC (permalink / raw)
To: Rodrigo De Leon <rdeleonp@gmail.com>; +Cc: pgsql-sql
On 7/7/06, Rodrigo De Leon <rdeleonp@gmail.com> wrote:
>
> On 7/7/06, T E Schmitz <mailreg@numerixtechnology.de> 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
> >
> > Actually, the pattern is more complex than that and I cannot see how I
> > can express it without brackets.
>
> Maybe:
>
> select
> substring ('150mm LD AD Asp XR Macro', '^[\\d-]*mm' ) as BASE_NAME
> , substring('150mm LD AD Asp XR Macro','^[\\d-]*mm (.*)$') as SUFFIX;
>
> select
> substring ('28-70mm LD AD Asp XR Macro', '^[\\d-]*mm' ) as BASE_NAME
> , substring('28-70mm LD AD Asp XR Macro','^[\\d-]*mm (.*)$') as SUFFIX;
>
> etc...
>
> Regards,
>
> Rodrigo
Is there a reason this column wasn't separated into two different columns?
Or perhaps into a child table if there could be more than one XXXmm value in
the field?
Just curious.
-Aaron
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: SELECT substring with regex
@ 2006-07-07 20:12 T E Schmitz <mailreg@numerixtechnology.de>
parent: Aaron Bono <postgresql@aranya.com>
0 siblings, 0 replies; 20+ messages in thread
From: T E Schmitz @ 2006-07-07 20:12 UTC (permalink / raw)
To: Aaron Bono <postgresql@aranya.com>; pgsql-sql
Aaron Bono wrote:
> On 7/7/06, *Rodrigo De Leon* <rdeleonp@gmail.com
> <mailto:rdeleonp@gmail.com>> wrote:
>
> On 7/7/06, T E Schmitz <mailreg@numerixtechnology.de
> <mailto:mailreg@numerixtechnology.de>> 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
> >
> > Actually, the pattern is more complex than that and I cannot see
> how I
> > can express it without brackets.
>
> Maybe:
>
> select
> substring ('150mm LD AD Asp XR Macro', '^[\\d-]*mm' ) as BASE_NAME
> , substring('150mm LD AD Asp XR Macro','^[\\d-]*mm (.*)$') as SUFFIX;
>
> select
> substring ('28-70mm LD AD Asp XR Macro', '^[\\d-]*mm' ) as BASE_NAME
> , substring('28-70mm LD AD Asp XR Macro','^[\\d-]*mm (.*)$') as SUFFIX;
>
> etc...
>
> Regards,
>
> Rodrigo
>
>
> Is there a reason this column wasn't separated into two different
> columns? Or perhaps into a child table if there could be more than one
> XXXmm value in the field?
>
> Just curious.
You're absolutely right (see my other posting):
what was entered:
MODEL.NAME "150mm F4 E" TYPE.NAME -
MODEL.NAME "150mm F4 PE" TYPE.NAME -
what should've been entered:
MODEL.NAME "150mm F4"
TYPE.NAME "PE"
TYPE.NAME "E"
both referencing the same MODEL
--
Regards,
Tarlika Elisabeth Schmitz
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: SELECT substring with regex
@ 2006-07-07 20:34 Jim Buttafuoco <jim@contactbda.com>
parent: T E Schmitz <mailreg@numerixtechnology.de>
1 sibling, 0 replies; 20+ messages in thread
From: Jim Buttafuoco @ 2006-07-07 20:34 UTC (permalink / raw)
To: mailreg@numerixtechnology.de; pgsql-sql
use plperl
---------- Original Message -----------
From: T E Schmitz <mailreg@numerixtechnology.de>
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 <mailreg@numerixtechnology.de> 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
> >>
> >> Actually, the pattern is more complex than that and I cannot see how I
> >> can express it without brackets.
> >
> >
> > Maybe:
> >
> > select
> > substring ('28-70mm LD AD Asp XR Macro', '^[\\d-]*mm' ) as BASE_NAME
>
> 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 and has to
> be bracketed.
>
> If the brackets cannot be avoided in the expression, your original
> suggestion might come in handy though:
>
> SELECT
> substring (NAME, '^\\d+(-\\d+)?mm') AS BASE_NAME ,
> substr(
> NAME
> , char_length(
> substring (NAME, '^\\d+(-\\d+)?mm')
> ) + 2
> ) AS SUFFIX
>
> Still, I'd be interested to know whether there is a 'more elegant' solution.
>
> --
>
> Regards,
>
> Tarlika Elisabeth Schmitz
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
------- End of Original Message -------
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: SELECT substring with regex
@ 2006-07-07 20:49 T E Schmitz <mailreg@numerixtechnology.de>
parent: Erik Jones <erik@myemma.com>
0 siblings, 4 replies; 20+ messages in thread
From: T E Schmitz @ 2006-07-07 20:49 UTC (permalink / raw)
To: pgsql-sql
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
>>>> "150mm", "LD AD Asp XR Macro"
>>>>
>>>
>>>
>>> select substring('150mm LD AD Asp XR Macro','^\\d+mm') as BASE_NAME,
>>> substring('150mm LD AD Asp XR Macro','^\\d+mm (.*)$') as SUFFIX;
>>> base_name | suffix
>>> -----------+--------------------
>>> 150mm | LD AD Asp XR Macro
>>> (1 row)
>>>
>>> The brackets surround the required match
>> 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
>>
> Will the mm always be the end of the base name?
I had thought it best to simplify the problem for the purposes of the
mailing list but maybe I should supply the complete problem and describe
the purpose of the exercise:
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].
First of all I did a 5 table join to select those NAMEs which don't
follow the required pattern: the pattern describes a photographic lens
(focal length followed by lens speed (aperture)) and nothing else.
Unfortuantely, there are a few hundred occurences where a few attributes
have been appended which should have been stored elsewhere.
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 NAME ~
'^((\\d+(-\\d+)?)mm F((\\d+(\.\\d+)?)+(\.(\\d+(\.\\d+)?)+)?))\\D+$'
which gives me a listing of those candidates that need to be amended -
manually or otherwise.
Next, I wanted to produce a result which splits NAME into what it should
be (BASE) and attributes (SUFFIX). Maybe I can generate some SQL from
that to tidy up the data.
--
Regards,
Tarlika Elisabeth Schmitz
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: SELECT substring with regex
@ 2006-07-07 21:33 Richard Broersma Jr <rabroersma@yahoo.com>
parent: T E Schmitz <mailreg@numerixtechnology.de>
3 siblings, 0 replies; 20+ messages in thread
From: Richard Broersma Jr @ 2006-07-07 21:33 UTC (permalink / raw)
To: mailreg@numerixtechnology.de; pgsql-sql
> 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 NAME ~
> '^((\\d+(-\\d+)?)mm F((\\d+(\.\\d+)?)+(\.(\\d+(\.\\d+)?)+)?))\\D+$'
>
>
> which gives me a listing of those candidates that need to be amended -
> manually or otherwise.
>
> Next, I wanted to produce a result which splits NAME into what it should
> be (BASE) and attributes (SUFFIX). Maybe I can generate some SQL from
> that to tidy up the data.
would this give you the results you want? It admit that it doesn't look to elegant.
name:
substr(your_string, 0, strpos(your_string, ' ')+1)
suffix:
substr(your_string, length(your_string)-strpos(your_string, ' '), length(your_string))
Regards,
Richard Broersma Jr.
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: SELECT substring with regex
@ 2006-07-07 22:39 Aaron Bono <postgresql@aranya.com>
parent: T E Schmitz <mailreg@numerixtechnology.de>
3 siblings, 1 reply; 20+ messages in thread
From: Aaron Bono @ 2006-07-07 22:39 UTC (permalink / raw)
To: mailreg@numerixtechnology.de; +Cc: pgsql-sql
On 7/7/06, T E Schmitz <mailreg@numerixtechnology.de> 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 recommend you alter the GUI to NOT allow any more bad data to get in
- perhaps give some nice validation and friendly error message about the
correct format or give various fields that need to be filled out so the user
can easily enter it and the GUI assembles the correct string. It won't fix
the current bad data but would stop the insanity from proliferating :)-
First of all I did a 5 table join to select those NAMEs which don't
> follow the required pattern: the pattern describes a photographic lens
> (focal length followed by lens speed (aperture)) and nothing else.
> Unfortuantely, there are a few hundred occurences where a few attributes
> have been appended which should have been stored elsewhere.
>
>
> 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 NAME ~
> '^((\\d+(-\\d+)?)mm F((\\d+(\.\\d+)?)+(\.(\\d+(\.\\d+)?)+)?))\\D+$'
>
>
> which gives me a listing of those candidates that need to be amended -
> manually or otherwise.
>
> Next, I wanted to produce a result which splits NAME into what it should
> be (BASE) and attributes (SUFFIX). Maybe I can generate some SQL from
> that to tidy up the data.
You might also consider adding the base and suffix columns with a trigger
that parses the name field and sets the values of base and suffix and also
does any integrity checks during inserts and updates. Finally, after the
data is cleaned up and the client apps are changed to use base and suffix
and not name, get rid of the name column.
Then again, this may be exactly what you are already trying to do.
-Aaron
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: SELECT substring with regex
@ 2006-07-08 01:24 Rodrigo De Leon <rdeleonp@gmail.com>
parent: T E Schmitz <mailreg@numerixtechnology.de>
1 sibling, 1 reply; 20+ messages in thread
From: Rodrigo De Leon @ 2006-07-08 01:24 UTC (permalink / raw)
To: pgsql-sql
On 7/7/06, T E Schmitz <mailreg@numerixtechnology.de> 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 and has to
> be bracketed.
>
> ...
>
> Still, I'd be interested to know whether there is a 'more elegant' solution.
OK, last try using regex:
SELECT
NAME
, substring(NAME, '^(\\d+(-\\d+)?mm)') AS BASE_NAME
, regexp_replace(NAME, '^\\d+(-\\d+)?mm (.*)', '\\2') AS SUFFIX
FROM MODEL
Regards,
Rodrigo
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: SELECT substring with regex
@ 2006-07-08 09:40 T E Schmitz <mailreg@numerixtechnology.de>
parent: Rodrigo De Leon <rdeleonp@gmail.com>
0 siblings, 1 reply; 20+ messages in thread
From: T E Schmitz @ 2006-07-08 09:40 UTC (permalink / raw)
To: Rodrigo De Leon <rdeleonp@gmail.com>; +Cc: pgsql-sql
Rodrigo De Leon wrote:
> On 7/7/06, T E Schmitz <mailreg@numerixtechnology.de> 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 and has to
>> be bracketed.
>>
>> ...
>>
>> Still, I'd be interested to know whether there is a 'more elegant'
>> solution.
>
>
> OK, last try using regex:
>
> SELECT
> NAME
> , substring(NAME, '^(\\d+(-\\d+)?mm)') AS BASE_NAME
> , regexp_replace(NAME, '^\\d+(-\\d+)?mm (.*)', '\\2') AS SUFFIX
> FROM MODEL
Is regexp_replace a new feature?
I am running v 7.4.
--
Regards,
Tarlika
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: SELECT substring with regex
@ 2006-07-08 15:40 T E Schmitz <mailreg@numerixtechnology.de>
parent: Aaron Bono <postgresql@aranya.com>
0 siblings, 0 replies; 20+ messages in thread
From: T E Schmitz @ 2006-07-08 15:40 UTC (permalink / raw)
To: Aaron Bono <postgresql@aranya.com>; +Cc: pgsql-sql
Aaron Bono wrote:
> On 7/7/06, *T E Schmitz* <mailreg@numerixtechnology.de
> <mailto:mailreg@numerixtechnology.de>> 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 recommend you alter the GUI to NOT allow any more bad data to
I absolutely agree that the user should not be able to enter 'bad' data
in the first place.
However, in this case it is not a hard and fast rule, rather a
suggestion how to organize the data sensibly. The pattern is suitable
for 98% of the lenses; there are some, albeit very few, exceptions.
Also: the MODEL.NAME is a free-form field; only when referred to by a
PRODUCT which is of CATEGORY 'Lens', would the pattern apply.
The only way one could maybe assist the user would be a warning when he
tries to save a 'Lens' PRODUCT, which deviates from the pattern.
> You might also consider adding the base and suffix columns with a
> trigger that parses the name field and sets the values of base and
> suffix and also does any integrity checks during inserts and updates.
> Finally, after the data is cleaned up and the client apps are changed to
> use base and suffix and not name, get rid of the name column.
The suffix should have been entered in TYPE.NAME, which references
MODEL. The structure exists, it just needs to be used efficiently.
--
Regards,
Tarlika
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: SELECT substring with regex
@ 2006-07-08 16:48 Rodrigo De Leon <rdeleonp@gmail.com>
parent: T E Schmitz <mailreg@numerixtechnology.de>
0 siblings, 0 replies; 20+ messages in thread
From: Rodrigo De Leon @ 2006-07-08 16:48 UTC (permalink / raw)
To: pgsql-sql
On 7/8/06, T E Schmitz <mailreg@numerixtechnology.de> 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/pgsql-patches/2005-07/msg00260.php
And the release notes:
http://www.postgresql.org/docs/8.1/static/release.html
I'd say at least:
7.4.9
8.0.4
8.1
Anyone care to verify this?
Regards,
Rodrigo
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: SELECT substring with regex
@ 2006-07-10 05:11 Emils <gnudiff@gmail.com>
parent: T E Schmitz <mailreg@numerixtechnology.de>
3 siblings, 1 reply; 20+ messages in thread
From: Emils @ 2006-07-10 05:11 UTC (permalink / raw)
To: pgsql-sql
2006/7/7, T E Schmitz <mailreg@numerixtechnology.de>:
>
> 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 (.+)$" for SUFFIX
Or are you having to deal with malformatted data too (like "30 -70mm"
or "28 mm ")?
Emils
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: SELECT substring with regex
@ 2006-07-10 11:02 T E Schmitz <mailreg@numerixtechnology.de>
parent: Emils <gnudiff@gmail.com>
0 siblings, 0 replies; 20+ messages in thread
From: T E Schmitz @ 2006-07-10 11:02 UTC (permalink / raw)
To: emils.klotins@gmail.com; pgsql-sql
Emils wrote:
> 2006/7/7, T E Schmitz <mailreg@numerixtechnology.de>:
>
>>
>> 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 (.+)$" for SUFFIX
>
> Or are you having to deal with malformatted data too (like "30 -70mm"
> or "28 mm ")?
There were quite a few malformed MODEL.NAMEs (upper/lower case,
additional blanks, missing mm) and therefore a precise regexp was required.
I did this as a 2-stage process:
WHERE !~ '^((\\d+(-\\d+)?)mm F((\\d+(\.\\d+)?)+(\.(\\d+(\.\\d+)?)+)?))$'
selected all malformed MODEL.NAMEs
I corrected the malformed basenames, which left me with the BASE/SUFFIX
problem only.
I selected all of those and /knowing/ that none of them were malformed,
I used a simplified pattern without round brackets to produce the BASE
and SUFFIX result columns:
substring (MODEL.MODEL_NAME, '^[-\\d]+mm F[-\\d\.]+' ) as BASE,
substring (MODEL.MODEL_NAME, '^[-\\d]+mm F[-\\d\.]+ *(.*)$') as SUFFIX
exported that together with PKs as CSV and generated SQL from it.
Basically, my problem had been that I needed to express the SUFFIX
pattern without using brackets for the BASE.
I would like to thank everyone who contributed to this thread.
--
Regards/Gruß,
Tarlika Elisabeth Schmitz
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: SELECT substring with regex
@ 2006-07-10 18:29 Alvaro Herrera <alvherre@commandprompt.com>
parent: T E Schmitz <mailreg@numerixtechnology.de>
3 siblings, 0 replies; 20+ messages in thread
From: Alvaro Herrera @ 2006-07-10 18:29 UTC (permalink / raw)
To: T E Schmitz <mailreg@numerixtechnology.de>; +Cc: pgsql-sql
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 take this picture
http://people.planetpostgresql.org/mha/index.php?/archives/97-Conference-is-over....html
seems to have a lot of aberration (I don't know the technical term,
sorry). Notice that grid on the floor? It looked very straight on the
real life. I'd expect a bit of curvature but I think that's too much.
Sorry for the offtopic :-)
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
^ permalink raw reply [nested|flat] 20+ messages in thread
end of thread, other threads:[~2006-07-10 18:29 UTC | newest]
Thread overview: 20+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-07-07 13:51 SELECT substring with regex T E Schmitz <mailreg@numerixtechnology.de>
2006-07-07 15:55 ` Rodrigo De Leon <rdeleonp@gmail.com>
2006-07-07 15:59 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
2006-07-07 17:35 ` T E Schmitz <mailreg@numerixtechnology.de>
2006-07-07 18:37 ` Rodrigo De Leon <rdeleonp@gmail.com>
2006-07-07 19:23 ` T E Schmitz <mailreg@numerixtechnology.de>
2006-07-07 20:34 ` Jim Buttafuoco <jim@contactbda.com>
2006-07-08 01:24 ` Rodrigo De Leon <rdeleonp@gmail.com>
2006-07-08 09:40 ` T E Schmitz <mailreg@numerixtechnology.de>
2006-07-08 16:48 ` Rodrigo De Leon <rdeleonp@gmail.com>
2006-07-07 19:45 ` Aaron Bono <postgresql@aranya.com>
2006-07-07 20:12 ` T E Schmitz <mailreg@numerixtechnology.de>
2006-07-07 18:58 ` Erik Jones <erik@myemma.com>
2006-07-07 20:49 ` T E Schmitz <mailreg@numerixtechnology.de>
2006-07-07 21:33 ` Richard Broersma Jr <rabroersma@yahoo.com>
2006-07-07 22:39 ` Aaron Bono <postgresql@aranya.com>
2006-07-08 15:40 ` T E Schmitz <mailreg@numerixtechnology.de>
2006-07-10 05:11 ` Emils <gnudiff@gmail.com>
2006-07-10 11:02 ` T E Schmitz <mailreg@numerixtechnology.de>
2006-07-10 18:29 ` Alvaro Herrera <alvherre@commandprompt.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox