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 3 6528
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
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
(888) 512-2048
"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
"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 (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 ;-) This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Tim Smith |
last post by:
I am looking to extract form element values from html, more generally
I have a substring that identifies the beginning of a value and a
string that identifies the end of value and I need to extract...
|
by: Robert Schultz |
last post by:
I have a C/C++ file that I simply want to 'extract' a function from.
Something like: extract <function name> <c or cpp file>
I want it to return from the beginning of the function, to the end.
...
|
by: Amit Khemka |
last post by:
Hello All,
say you have some string: ", foobar "
Now i want to extract all substrings for which
"isinstance(eval(substr), list)" is "True" .
now one way is to walk through the whole sample...
|
by: nick |
last post by:
Hi all
can any one please tell me what is wrong in this code??
I'm new to deal with text files and extract data.
i'm trying to look for data in a text file (3~4 pages) some lines start
with a...
|
by: teo |
last post by:
I have a problem (partial).
Some days ago I asked for a way
to extract a word and few text around it
(30 chars on the left and 30 on the right)
from a long text.
I went good with:
...
|
by: flit |
last post by:
Hello All,
Using poplib in python I can extract only the headers using the .top,
there is a way to extract only the message text without the headers?
like remove the fields below:
"...
|
by: Umesh |
last post by:
I want to extract a string abc*xyz from a text file.
* indicates arbitrary no. of characters.
I'm only able to do it when the string has definite no. of characters
or the string length is...
|
by: Alberto Sartori |
last post by:
Hello,
I have a html text with custom tags which looks like html comment,
such:
"text text text <p>text</ptext test test
text text text <p>text</ptext test test
<!-- @MyTag@ -->extract...
|
by: Bernard Drolet |
last post by:
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 *+
I...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
| |