| Author |
Topic |
|
ronanpcs
Starting Member
6 Posts |
 Posted - 2007-04-09 : 14:16:40
|
| I have a field 'LastName' in a SQL 2000 table 'customers' that is populated with all our customer's full names in this order:Smith John MI need to flip the information around so that it can be exported into a CSV file for an automated email notification program to notify them their items are in and having it in first, last, MI format is much more professional.I've been playing with charindex and stuff routines and am spinning my wheels. Is there a canned routine for something like this - this must have been done a hundreed times out there in SQL somewhere (or it's so easy to do that everyone (but me) just cranks it from scratch).Thanks in advance for your help.ronanpcs |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-04-09 : 14:19:49
|
| How is the information coming in to the table, in the first place. May be thats where you should look to restrict lastname to lastname values. Other than that, once the data is in with "aaa bbb x" format how do you know whether aaa is the firstname or is it bbb? unless you manually look at it and can figure it out.************************Life is short. Enjoy it.************************ |
 |
|
|
ronanpcs
Starting Member
6 Posts |
Posted - 2007-04-09 : 14:25:49
|
| Unfortunately, the application that populates the table does all customers in lastname (space) firstname (space) middlename/initial format. It is always that way and that application is fine with it. However, for my purposes, I need to change the data around and then save the results (along with a few other fields) to a CSV file.ronanpcs |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-04-09 : 14:32:05
|
| declare @t varchar(50), @lname varchar(50), @fname varchar(50) ,@middle varchar(5)set @t = 'lastname firstname m'set @lname = left(@t, charindex(' ',@t))select @lname Set @t = replace(@t, @lname, '')set @fname = left(@t, charindex(' ',@t))set @fname = left(@t, charindex(' ',@t))select @fnameset @t = replace(@t, @fname, '')set @middle = @tselect @middle************************Life is short. Enjoy it.************************ |
|
|
|
ronanpcs
Starting Member
6 Posts |
Posted - 2007-04-10 : 09:14:12
|
| Thanks for the help! We'll give it a shot.ronanpcs |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-04-10 : 09:17:02
|
| What about "multiple" last names, such as Van Der Hook?Or Doe, Dr John?Peter LarssonHelsingborg, Sweden |
|
|
|
ronanpcs
Starting Member
6 Posts |
Posted - 2007-04-10 : 09:27:18
|
| You're right Peter. I have customers with multiword last names (hyphenated, Von Lueven, etc.). Not only that, but it turns out this particular database is actually Sybase and has no REPLACE function. Otherwise, it's running most SQL functions. Sorry for the incorrect database type.ronanpcs |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-04-10 : 09:43:16
|
| You write you have problem with LASTNAME column, where ALL names seems to be stored.Do you have a FIRSTNAME column too? If so, you can compare which words already is in first name and exclude them from the LASTNAME column.Peter LarssonHelsingborg, Sweden |
|
|
|
ronanpcs
Starting Member
6 Posts |
Posted - 2007-04-10 : 09:48:50
|
| No other name fields. The application stuffs everything into that field. Why I don't know, but it's how it works.ronanpcs |
|
|
|
|
Field content reversal