| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: How can I find a space in a text string
Helmut,
The INSTR function might be the key thing you are looking for. If that's all you need to get on the right track, ignore the rest of this e-mail since it provides details of a few of the approaches you might want to take.
I am brain cramping right now because it seems like there is a "shorter" method than what I am about to present; but, I can't think of it right now. Anyway, the following seems to work:
SQL> select cut_me,
2 substr(cut_me,1, 3 decode(instr(cut_me,' '),0,Length(cut_me), 4 instr(cut_me,' ')-1)) Cut5 from cut_space
CUT_ME CUT
-------------------- --------------------
VISA 123456789 VISA
HOW NOW BROWN COW HOW
XXX XXX
OR, you might prefer the following:
1 select cut_me,
2 Decode(instr(cut_me,' '),0,cut_me, 3 substr(cut_me,1,instr(cut_me,' ')-1))cut
XXX XXX
There are a multitude of ways you can code it.
The key is to use the INSTR function to find the space. If a space is *not* found, INSTR returns a 0. Just SUBSTR to the LENGTH (or in the second example since SUBSTR is *inside* the DECODE, just accept the value). If a space *is* found, SUBSTR to the INSTR return value minus 1.
I will probably feel foolish later; but, it seems like there is a more succinct method. Anyway, the above should get you pointed in the right direction. If you feel like the technique will be used often, you might want to create a PL/SQL Function that does the same thing. It prevents you from having to code the logic in multiple SQL statements. I have needed to do such a thing during data conversion activities, and, during loads of external data into a DW. A simple sample function follows:
Create Or Replace Function Split (p_value varchar2) Return VarChar2 Is
l_return varchar2(100);
l_position PLS_INTEGER := INSTR(p_value,' ');
Begin
If l_position = 0 Then
l_return := p_value;
Else
l_return := SUBSTR(p_value,1,l_position-1);
End If;
RETURN(l_return);
End;
/
1 select cut_me, split(cut_me) split
2* from cut_space
SQL> /
CUT_ME SPLIT
-------------------- --------------------
VISA 123456789 VISA
HOW NOW BROWN COW HOW
XXX XXX
You can really dress up such a function so that you pass it the delimiter character as opposed to hard coding looking for a space. You can also enhance it by passing in which occurrence number, etc. There are lots of things you can do to make it multi-purpose and flexible. Such routines have been very helpful in the past when dealing with external data, data conversions, and so on.
Regards,
Larry G. Elkins
The Elkins Organization Inc.
elkinsl_at_flash.net
214.954.1781
-----Original Message-----
From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Helmut
Daiminger
Sent: Friday, August 18, 2000 7:30 PM
To: Multiple recipients of list ORACLE-L
Subject: How can I find a space in a text string
Hi all!
How can I find the first blank space in a given text string?
E.g.: "VISA 123456789"
I want to find the first blank (space) in the text string and then cut out the left part of the string (in the example: VISA).
How do I do that?
Thanks,
Helmut
-- Author: Helmut Daiminger INET: hdaiminger_at_vivonet.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing ListsReceived on Fri Aug 18 2000 - 19:56:36 CDT
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may
 |
 |