That's what
instr(field_name,' ')
does in this query.

INSTR(arg1,arg2) returns the first position of arg2 within arg1.
Use a looping structure to find the positions of all occurrances of arg2
within arg1.

-----Original Message-----
From: Howerton Jim
[mailto:oracledba-ezmlmshield-x59824724.[Email address protected]
Sent: Tuesday, August 29, 2006 4:58 PM
To: LazyDBA Discussion
Subject: RE: sql help

Is there an Oracle counterpart to t-sql charindex to locate the position
of the space? Then do a substring from 1 to the location of the space
minus one. Then a substring from the location of the space plus one to
the end.

-----Original Message-----
From: Jeff Chirco
[mailto:oracledba-ezmlmshield-x70051354.[Email address protected]
Sent: Tuesday, August 29, 2006 3:23 PM
To: LazyDBA Discussion
Subject: RE: sql help

Great, but that returns the last name. How do you do it for the part in
front of the space?

-----Original Message-----
From: Raymundo Imoff
[mailto:oracledba-ezmlmshield-x67263129.[Email address protected]
Sent: Tuesday, August 29, 2006 12:02 PM
To: LazyDBA Discussion
Subject: Re: sql help

select substr(field_name,instr(field_name,' ')+1) from table;


------ Original Message ------
Received: 29/08/2006 01:42 PM CDT
From: "Jeff Chirco "
<oracledba-ezmlmshield-x39232331.[Email address protected]
To: "LazyDBA Discussion" <[Email address protected]
Subject: sql help

> I have a field in my database that stores the full First and Last name

> like BOB SMITH.
>
> I want to write a sql statement that will return everything in front
of
> the space. So I just want to return the first name BOB. How can I do

> this in 9i? Or can it only be done in 10g with regular expressions?
>
>
>
> Jeff
>
>
>
>
>
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post

> a dba job: http://jobs.lazydba.com To Subscribe :
> http://www.LazyDBA.com To unsubscribe:
> http://www.lazydba.com/unsubscribe.html
>
>
>





---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a
dba job: http://jobs.lazydba.com To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html




---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a
dba job: http://jobs.lazydba.com To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html





---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a
dba job: http://jobs.lazydba.com To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html



Oracle LazyDBA home page