Extract the beginning of a string

[Bernard Drolet]

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

[VC]

Hello Bernard,

You can do it so:

select translate('xyz174',

'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvw xyz0123456789',
'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvw xyz')
from dual;

Rgds.

"Bernard Drolet" <le****@yahoo.ca> wrote in message
news:ee**************************@posting.google.c om...

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

[mcstock]

or, if the last portion is all numeric

rtrim( theString, '0123456789')

or if, the last portion is not all numeric

substr(
theString
,1
,instr(
translate(
theString
,'0123456789'
,'0000000000'
)
, '0'
) -1
)

translate replaces all digits with '0'
instr finds the position of the first '0'
this decremented and used as the length for substr

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

--
Mark C. Stock
email mcstock -> enquery(dot)com
www.enquery.com
[phone]
"VC" <bo*******@hotmail.com> wrote in message
news:_RAqb.131051$HS4.1031371@attbi_s01...

Hello Bernard,

You can do it so:

select translate('xyz174',

'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvw xyz0123456789',
'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvw xyz')
from dual;

Rgds.

"Bernard Drolet" <le****@yahoo.ca> wrote in message
news:ee**************************@posting.google.c om...

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

[Bernard Drolet]

"mcstock" <mc*************@spamdamenquery.com> wrote in message news:<h9********************@comcast.com>...

or, if the last portion is all numeric

rtrim( theString, '0123456789')

or if, the last portion is not all numeric

substr(
theString
,1
,instr(
translate(
theString
,'0123456789'
,'0000000000'
)
, '0'
) -1
)

translate replaces all digits with '0'
instr finds the position of the first '0'
this decremented and used as the length for substr

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

--
Mark C. Stock
email mcstock -> enquery(dot)com
www.enquery.com
[phone]

Hi Mark, you guessed right,

my string contains more than just text;
a real example would like as 'AMM05-10-00-210-801-A01';
your solution worked perfectly.
Thank you

Bernard Drolet

PS: I implemented it in a function, as suggested ;-)