Re: How to extract single characters in PL/SQL?

[Quoted] A not uncommon problem when using instr to parse names..We solved it by not allowing spaces in the name! Von Danikan would be VonDanikan in our personnel system ( names are stored Last,First MIorName) . With a space, our parse code would assume Danikan was his middle name. I suppose you could look for 2 ( or more) spaces after the comma and deal with those cases so that people with 4 parts to their name e.g. Lewis,William Thomas Chauncey , could be dealt with. Ideally, the data input should request First, Last and Middle separately and concatenate them into the format you need.  

Karsten Farrell <kfarrell_at_belgariad.com> wrote:

>Karsten Farrell wrote:
>> Guido Konsolke wrote:
>>
>>> Hallo Ludwig,
>>>
>>> as usual:
>>> 1. Please provide always the version of OS, RDBMS etc.
>>> 2. There's more than one way. Here's a funny one from a co-worker
>>> (sorry for wraps):
>>>
>>> select substr('Albert Einstein',1,instr('Albert Einstein',' ')-1) from
>>> dual
>>> union all
>>> select substr('Albert Einstein',instr('Albert Einstein','
>>> ')+1,length('Albert Einstein') -instr('Albert Einstein',' ')) from dual
>>>
>>> hth,
>>> Guido
>>>
>>> "Ludwig Nörg" <noergl_at_gmx.net> schrieb im Newsbeitrag
>>> news:a7148654.0212120123.2e47ca18_at_posting.google.com...
>>>
>>>> Hi,
>>>>
>>>> I need to split a varchar2 field containing name and first name
>>>> combined into 2 fields, a blank as separator.
>>>>
>>>> How can I do this in Oracle SQL or PL/SQL?
>>>>
>>>> Any suggestions is helpful.
>>>>
>>>> Thanks
>>>> Ludwig Nörg
>>>
>>>
>>>
>>>
>> And hope you don't run into Erich Von Danikan in your database. :)
>>
>BTW, in case someone doesn't catch it ... I'm not referring to the fact
>that Von Danikan corresponds with aliens ... I'm referring to the fact
>that he has a space in his last name.
Received on Thu Dec 12 2002 - 21:05:53 CET