Postgres Pro
Downloads
Home   >   mailing lists

Thread: Regular Expression Question

Regular Expression Question

From
Terry Lee Tucker
Date:
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 "numbers" can possibly be something other than numbers alone as
in the SID: part of the string above. I have come up with a way of extracting
both pieces of information where, at least in my mind, the key parameters are
the colon (:) and a space, as in the first case, or asterik (*), as in the
second case, marking the end of the string to extract.

This one extracts the first value:
rnd=# select substring ('DUN: 006235835 SID: KT-3616*' from '^.+?:(.+?) ');
 substring
------------
  0062358
(1 row)

This one extracts the second value;
rnd=# select substring ('DUN: 006235835 SID: KT-3616*' from
'^.+?:.+?:(.+?)\\*');
 substring
-----------
  KT-3616
(1 row)

I keep thinking there is a better way to do this. It has taken me several
hours just to get to this point. So, I would love to see any suggestions as
to improvments on this. I just know it could be better implemented.
rnd=# select version();
                                                   version
--------------------------------------------------------------------------------------------------------------
 PostgreSQL 7.4.6 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 3.2.3
20030502 (Red Hat Linux 3.2.3-49)
(1 row)

Thanks for the input...

Re: Regular Expression Question

From
"Karl O. Pinc"
Date:
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 one variable and the KT-3616 into
>
> another. Both "numbers" can possibly be something other than numbers
> alone as
> in the SID: part of the string above. I have come up with a way of
> extracting
> both pieces of information where, at least in my mind, the key
> parameters are
> the colon (:) and a space, as in the first case, or asterik (*), as in
> the
> second case, marking the end of the string to extract.

I would tend to use split_part() and avoid regular expressions
altogether.

select split_part('DUN: 006235835 SID: KT-3616*', ' ', 2);



Karl <kop@meme.com>
Free Software:  "You don't pay back, you pay forward."
                  -- Robert A. Heinlein

Re: Regular Expression Question

From
Thomas Pundt
Date:
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 006235835 into one variable and the KT-3616 into
|  another. Both "numbers" can possibly be something other than numbers
| alone as in the SID: part of the string above. I have come up with a way
| of extracting both pieces of information where, at least in my mind, the
| key parameters are the colon (:) and a space, as in the first case, or
| asterik (*), as in the second case, marking the end of the string to
| extract.

given that the strings SID: and DUN: won't change, you could use the
following two RE's:

'SID: (.*?)\\*' for extracting the SID part, and 'DUN: (.*?) ' for
extracting the DUN part.

Ciao,
Thomas

----------------------------------------------------------------
Thomas Pundt <thomas@pundt.de> -- http://www.pundt.de