| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Diff quest about space in a text string
Larry,
Will these scripts work on for special characters as well? I need to make a change to some corrupt data that has embedded '\240' (octal value, '160' ascii value) instead of space to separate the words. The data looks like (for example): 1234\240Sunset\240Drive..... and needs to look like 1234 Sunset Drive. Will your scripts find these finds of characters that are "hidden" (you can only see that something's wrong when you vi the listing from a spool of the data, not when you just select in sql*plus).
Scripting is one of my biggest weaknesses, so any help would be appreciated. I need to put this into a pl/sql script. I can build the declare section, open and close a cursor, code an exception handling message, and I know how to compile and test... BUT what I *don't* know is to do is code the part that would take the strings and parse through them, and find and replace these hidden octal/ascii chars with spaces.
TIA,
Tamara Swilley
AGENCY.COM
Avon, Colorado 81620
tswilley_at_agency.com
-----Original Message-----
From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Larry G.
Elkins
Sent: Friday, August 18, 2000 8:08 PM
To: Multiple recipients of list ORACLE-L
Subject: 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-----
Daiminger
Sent: Friday, August 18, 2000 7:30 PM
To: Multiple recipients of list ORACLE-L
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 Liststo: 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 also send the HELP command for other information (like subscribing).
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
--
Author: Larry G. Elkins
INET: elkinsl_at_flash.net
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Liststo: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L Received on Tue Aug 22 2000 - 11:18:34 CDT
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
 |
 |