First Name, Middle Initial...

[Kimby]

I'm sure I'm out of my element asking this question, how do I fix this problem:<br>I have first names consisting of two name with a space between and a middle name.&nbsp;&nbsp;The problem seems to be that the first name (an initial), and the middle name (spelled out)...I get the middle name alpha'd in with the spelled out first names.&nbsp;&nbsp;For example:<br><br>Ann Smith<br>Anna Smith <br>W Annaite Smith<br>Annette Smith<br><br>Also, I want the first names,containing two names, separated by a space and middle name 'Anna Marie Elizabeth Smith'instead of the two names together with no space, then the middle name 'AnnaMarie Elizabeth Smith'. <br>I know it's confusing, but I'm totally new to all this.&nbsp;&nbsp;I know it can be done, just don't know how.<br><br>Thanks for any help, Kim<br>

 

[DougP]

I'm not sure if this can be done in SQL. If it can it will be one very complex T-SQL statement.<br>If you have Access or Visual Basic you can link to the SQL data with ODBC.<br>These programs allow you to write functions in Visual Basic language which will do a very good job with that. And a while ago soemone in the Access forum had a solution for thaty exact problem. <p>DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br> Ask me how Bar-codes can help you be more productive.

 

[joffy]

Try using some inbuilt functions like instr, length etc. These appear in MYSQL and Oracle certainly, and may be available in other flavours

 

[THoey]

I am not really sure what you are asking for, but I will make a guess.&nbsp;&nbsp;&nbsp;I gather you have one long text field that contains the full name.&nbsp;&nbsp;&nbsp;I also guess that you are trying to parse through it to be able to print last name first, etc, or to break it up into individual fields.&nbsp;&nbsp;&nbsp;<br><br>Using a numeric variable as a pointer and the INSTR and SUBSTR functions, you could walk through the text field backwards and break the information up.&nbsp;&nbsp;&nbsp;I am not sure what flavor of SQL you are using, so I will try to explain using pseudo-code (does anyone use that any more?) and Oracle SQL.<br><br>Say we have the following string that we want to parse - &quot;Billy Joe Bob Red-Neck&quot;&nbsp;&nbsp;&nbsp;<br><br>ASSUMPTIONS -<br>-&nbsp;&nbsp;&nbsp;All names will have a value (Not Null).&nbsp;&nbsp;&nbsp;<br>-&nbsp;&nbsp;&nbsp;All names will be entered first, middle, and then last.<br>-&nbsp;&nbsp;&nbsp;Any double last names (due to marriage?) will be separated by a &quot;-&quot; not a space.<br>-&nbsp;&nbsp;&nbsp;There are no Titles, like &quot;Dr.&quot; and no extensions like &quot;Jr.&quot;&nbsp;&nbsp;&nbsp;If you must handle those, it might be easier to make a list of the most common titles and extensions, search for them first, and then remove them from the rest of the name string.<br><br><b><br>/* Declare and Initialize Name Strings */<br>long_name&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;varchar2&nbsp;&nbsp;&nbsp;&quot;Billy Joe Bob Red-Neck&quot;<br>last_name&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;varchar2&nbsp;&nbsp;&nbsp;&quot;&quot;<br>first_name&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;varchar2&nbsp;&nbsp;&nbsp;&quot;&quot;<br>middle_name&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;varchar2&nbsp;&nbsp;&nbsp;&quot;&quot;<br><br>/* Declare Variables */<br>space_ptr&nbsp;&nbsp;&nbsp;Number<br><br>/* Make sure there are no leading or trailing spaces */<br>long_name = LTRIM(RTRIM(long_name))<br><br>/* Get Last Name */<br><br>/* Starting from the end of the string, look for the */ <br>/* first space */<br>space_ptr = INSTR(long_name, &quot; &quot;, (LENGTH(long_name) * -1))<br><br>/* The last name is the charcters from the next position */<br>/* after the last space to the end of the string */<br>last_name = SUBSTR(long_name, space_ptr + 1)<br><br>/* Remove last name from long name */<br>long_name = SUBSTR(long_name, 1, (LENGTH(long_name) - (LENGTH(last_name) + 1))<br><br>/* Get Middle Name */<br><br>/* Starting from the end of the string, look for the */<br>/* first space */<br>space_ptr = INSTR(long_name, &quot; &quot;, (LENGTH(long_name) * -1))<br><br>/* The middle name is the charcters from the next */<br>/* position after the last space to the end of the */<br>/* string */<br>middle_name = SUBSTR(long_name, space_ptr + 1)<br><br>long_name = SUBSTR(long_name, 1, (LENGTH(long_name) - (LENGTH(middle_name) + 1))<br><br>/* All that is left must be the First Name */<br>first_name = long_name<br></b><br><br>Like I said, I am not sure if this is what you were asking or not.&nbsp;&nbsp;&nbsp;I hope it helps...<br> <p>Terry M. Hoey<br><a href=mailto:th3856@txmail.sbc.com>th3856@txmail.sbc.com</a><br><a href= > </a><br>