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]

Re: Extract the beginning of a string

Hello Bernard,

You can do it so:

select translate('xyz1 74',

'ABCDEFGHIJKLMN OPQRSTUVWXYZabc defghijklmnopqr stuvwxyz0123456 789',
'ABCDEFGHIJKLMN OPQRSTUVWXYZabc defghijklmnopqr stuvwxyz')
from dual;

Rgds.

"Bernard Drolet" <le_pul@yahoo.c awrote in message
news:ee7856eb.0 311061513.6c953 9e1@posting.goo gle.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

[mcstock]

Re: Extract the beginning of a string

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

https://www.enquery.com

(888) 512-2048


"VC" <boston103@hotm ail.comwrote in message
news:_RAqb.1310 51$HS4.1031371@ attbi_s01...

Hello Bernard,
>
You can do it so:
>
select translate('xyz1 74',
>
'ABCDEFGHIJKLMN OPQRSTUVWXYZabc defghijklmnopqr stuvwxyz0123456 789',
'ABCDEFGHIJKLMN OPQRSTUVWXYZabc defghijklmnopqr stuvwxyz')
from dual;
>
Rgds.
>
"Bernard Drolet" <le_pul@yahoo.c awrote in message
news:ee7856eb.0 311061513.6c953 9e1@posting.goo gle.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

>
>

[Bernard Drolet]

Re: Extract the beginning of a string

"mcstock" <mcstockspamplu g@spamdamenquer y.comwrote in message news:<h9GdnewQd fwrejeiRVn-iw@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

https://www.enquery.com

(888) 512-2048
>
>

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 ;-)