Re: divide column in to 2 columns were the 1st space is.

On Thu, 17 Jan 2008 12:58:47 -0800 (PST), Jeremy Smith <godtoall_at_hotmail.com> wrote:

>select street, substr(street,0, instr(street,' ')),
>substr(street,instr(street,' ')+1) from loc_address

Thanx for posting your own reply for others. Very nice.

One comment, the first INSTR() should have a -1 just like the second has a +1, otherwise it will include an extraeneous space:

WITH
        loc_address
AS

	(
	 SELECT '211 S. Akard Str.' AS Street FROM Dual UNION ALL
	 SELECT '2525 N. WATNEY WAY' AS Street FROM Dual
	)
select
	street,
	'|' || substr(street,0, instr(street,' ')) || '|',
	'|' || substr(street,instr(street,' ')+1) || '|'
from
	loc_address;

STREET             '|'||SUBSTR(STREET,0 '|'||SUBSTR(STREET,I
------------------ -------------------- --------------------
211 S. Akard Str.  |211 |               |S. Akard Str.|
2525 N. WATNEY WAY |2525 |              |N. WATNEY WAY|

B. Received on Fri Jan 18 2008 - 08:39:13 CST