Re: Extract the beginning of a string
Date: Thu, 6 Nov 2003 19:29:55 -0500
Message-ID: <h9GdnewQdfwrejeiRVn-iw_at_comcast.com>
or, if the last portion is all numeric
rtrim( theString, '0123456789')
or if, the last portion is not all numeric
substr(
theString
translate replaces all digits with '0'
if the numeric part is optional, you'll need to add a decode to check for an
instr value of 0
whatever you do, consider creating a stored function rather than putting
this expression directly into your SQL statement
,1
,instr(
translate(
theString
,'0123456789'
,'0000000000'
)
, '0'
) -1
)
instr finds the position of the first '0'
this decremented and used as the length for substr
--
Mark C. Stock
email mcstock -> enquery(dot)com
www.enquery.com
(888) 512-2048
"VC" <boston103_at_hotmail.com> wrote in message
news:_RAqb.131051$HS4.1031371_at_attbi_s01...
> Hello Bernard,
>
> You can do it so:
>
> select translate('xyz174',
>
> 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789',
> 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz')
> from dual;
>
> Rgds.
>
> "Bernard Drolet" <le_pul_at_yahoo.ca> wrote in message
> news:ee7856eb.0311061513.6c9539e1_at_posting.google.com...
> > Hi,
> >
> > I have a column containing a string; the string always starts with a
> > letter (a-z), followed by an undefined number of letters, then one
> > number or more.
> >
> > The REGEXP would look like [a-zA-Z][a-zA-Z]*[0-9]+
> >
> > I need to extract the letters at the beginning in an SQL query or in
> > PL/SQL
> >
> > For example, If I have
> >
> > abc123 -> abc
> >
> > a123 -> a
> >
> > abcdefgh1 -> abcdefgh
> >
> >
> >
> > Any suggestion ?
> >
> >
> > Thank you
> >
> >
> >
> > Bernard Drolet
>
>
Received on Fri Nov 07 2003 - 01:29:55 CET