In Oracle, use instr(). Check the manual. It's much more powerful than other versions of the same. For example, you can search starting from the end working left. And snatch the occurance you want. etc...

I'd look it up, but the server with my docs is down...

-----Original Message-----
From: Howerton Jim
[mailto:oracledba-ezmlmshield-x59824724.[Email address protected]
Sent: Tuesday, August 29, 2006 1: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




--
This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean.

Douglas County, Oregon
www.co.douglas.or.us


Oracle LazyDBA home page