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

Not allowing spaces in a name? What about two middle names? What about no first names, only initials? What about hyphenated names, lower-caps last names, last names only, first names only? We should be designing databases that support the existence of names other than our notion or perception or assumption of what a name or names really are.

Does your database support names like the following?: T.S. Elliot
Johan Verloren van Themaat
JD van Damme
Bhavani Prasad Polimetla
Lee Kwang-yul
Scott Tiger
John Burns Jr.
Lisa Lightninghouse III
Ahmed Mehr-Maybodi
Mary Jo Kopechnic

If not, why not? We have the means to.

TurkBear <john.greco_at_dot.state.mn.us> wrote in message news:<ijqhvukrgqd6rs86hhfvm83v0q7m59nb50_at_4ax.com>...
> 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 Mon Dec 23 2002 - 18:02:01 CET