Well, here is an interesting select statement to give you the values you
want.
select name,
length(name),
instr(name,' ',1),
instr(name,' ',1,2),
substr(name,1,instr(name,' ',1)-1),
substr(name,instr(name,' ',1)+1,(length(name)-instr(name,' ',1,1))-2),
substr(name,instr(name,' ',1,2)+1)
from name_test
This assume that all middle initials will be 1 defined character
(otherwise logic fails) and that no names will contain spaces, such as Mac
Donald. The logic looks for blank spaces and then counts strings from
those spaces. You get the idea of where to go from here, if not, give me
all the rules for the names and we can try to come up with something more
interesting. This is a select statement, but can easily be turned into an
insert statement.
CC
----------------------------------------------------------------------------------------
This is a PRIVATE message. If you are not the intended recipient, please
delete without copying and kindly advise us by e-mail of the mistake in
delivery. NOTE: Regardless of content, this e-mail shall not operate to
bind CSC to any order or other contract unless pursuant to explicit
written agreement or government initiative expressly permitting the use of
e-mail for such purpose.
----------------------------------------------------------------------------------------
Casey C Cook/ASD/CSC
@CSC
07/17/2003 04:46 PM
Please respond to cf-talk
To: CF-Talk <[EMAIL PROTECTED]>
cc:
Subject: Re: OT: Think you are a SQL GURU? Try figuring this one out.
If you always know that the names are in that format, then couldnt you do
something like the following very psuedo code.
----------------------------------------------------------------------------------------
This is a PRIVATE message. If you are not the intended recipient, please
delete without copying and kindly advise us by e-mail of the mistake in
delivery. NOTE: Regardless of content, this e-mail shall not operate to
bind CSC to any order or other contract unless pursuant to explicit
written agreement or government initiative expressly permitting the use of
e-mail for such purpose.
----------------------------------------------------------------------------------------
Wjatscheslaw Kravtchenko <slava
@hexabyte.org>
07/17/2003 04:25 PM
Please respond to cf-talk
To: CF-Talk <[EMAIL PROTECTED]>
cc:
Subject: OT: Think you are a SQL GURU? Try figuring this
one out.
SQL or Oracle syntax?
CC
Hey Eric,
It's not SQL it's more for PERL and Regular Expressions. I am not too
familiar with regular expressions in ColdFusion but I'm sure that they are
no RegEx in SQL. Another thing what could do it would be kdb from KX
Systems but again you need to learn k.
If you can drop me the csv file (please not more than 3 mb). I can have a
look.
SL
>Here is the challenge.
>
>Have a table with a column called NAME and the column contains both
>names of businesses and individuals. Business names are stored as is ,
>example ABC Company. Individuals are stored as last name first name
>middle initial, example Public John Q. I need to get this information
>into another table where the biz name can be stored in the first name
>field no problem but I need to parse and individuals name so I can put
>the individual's first name in the first name column and their last
>name in the last name column.
>
>Figure it out and I will send you a prize.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Get the mailserver that powers this list at
http://www.coolfusion.com
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
