Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
akpaga
Constraint Violating Yak Guru
331 Posts |
Posted - 2008-08-19 : 11:40:15
|
In one table monthlycustomers i have the name fields as firstname(cust_firstname)and last name like (cust_lastname) and in othe table called AllCustomers i have a field called cust_fullnamewhere the data is stored in this particualr manner lastname,firstname.What i want is to compare both the tables on the name field and see which customers of the montlycustomers table are not present in AllCustomers table.How can i do this in accurate manner? Thanks in advance |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-08-19 : 11:53:20
|
SELECT mc.*FROM MonthlyCustomers AS mcLEFT JOIN AllCustomers AS ac ON ac.CustID = mc.CustIDWHERE ac.CustID IS NULL E 12°55'05.25"N 56°04'39.16" |
 |
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-08-19 : 12:14:30
|
quote: Originally posted by akpaga In one table monthlycustomers i have the name fields as firstname(cust_firstname)and last name like (cust_lastname) and in othe table called AllCustomers i have a field called cust_fullnamewhere the data is stored in this particualr manner lastname,firstname.What i want is to compare both the tables on the name field and see which customers of the montlycustomers table are not present in AllCustomers table.How can i do this in accurate manner? Thanks in advance
SELECT mc.cust_firstname,mc.cust_lastnameFROM monthlycustomers mcLEFT JOIN AllCustomers aon a.cust_fullname=coalesce(cust_lastname,'')+','+coalesce(cust_firstname,'')WHERE a.cust_fullname IS NULL |
 |
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2008-08-19 : 12:34:35
|
...and then all you have to do is depend on the users to enter the full name strings consistently and correctly. What could go wrong with that?Boycott Beijing Olympics 2008 |
 |
|
akpaga
Constraint Violating Yak Guru
331 Posts |
Posted - 2008-08-19 : 12:46:07
|
quote: Originally posted by visakh16
quote: Originally posted by akpaga In one table monthlycustomers i have the name fields as firstname(cust_firstname)and last name like (cust_lastname) and in othe table called AllCustomers i have a field called cust_fullnamewhere the data is stored in this particualr manner lastname,firstname.What i want is to compare both the tables on the name field and see which customers of the montlycustomers table are not present in AllCustomers table.How can i do this in accurate manner? Thanks in advance
SELECT mc.cust_firstname,mc.cust_lastnameFROM monthlycustomers mcLEFT JOIN AllCustomers aon a.cust_fullname=coalesce(cust_lastname,'')+','+coalesce(cust_firstname,'')WHERE a.cust_fullname IS NULL
Firts Thanks for your response and to all who responded.I forgot ,the a.cust_fullname also have the suffixes stored such as mr,jr.the order is lastname,firstname suffix(no comma between firstname and lastname). Can i ignore the suffix and compare only on lastname and firstname. |
 |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-08-19 : 12:51:58
|
then how will be your fullname values. can you give some sample? |
 |
|
akpaga
Constraint Violating Yak Guru
331 Posts |
Posted - 2008-08-19 : 12:52:01
|
quote: Originally posted by akpaga
quote: Originally posted by visakh16 [quote]Originally posted by akpaga In one table monthlycustomers i have the name fields as firstname(cust_firstname)and last name like (cust_lastname) and in othe table called AllCustomers i have a field called cust_fullnamewhere the data is stored in this particualr manner lastname,firstname.What i want is to compare both the tables on the name field and see which customers of the montlycustomers table are not present in AllCustomers table.How can i do this in accurate manner? Thanks in advance
SELECT mc.cust_firstname,mc.cust_lastnameFROM monthlycustomers mcLEFT JOIN AllCustomers aon a.cust_fullname=coalesce(cust_lastname,'')+','+coalesce(cust_firstname,'')WHERE a.cust_fullname IS NULL
Firts Thanks for your response and to all who responded.I forgot ,the a.cust_fullname also have the suffixes stored such as mr,jr.the order is lastname,firstname suffix(no comma between firstname and lastname). Can i ignore the suffix and compare only on lastname and firstname.example : monthly customers table vincent(cust_lastname) john(cust_firstname)allcustomerstable -- vincent,john jr allcustome |
 |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-08-19 : 12:53:33
|
you told no comma but what you've given contains comma b/w first & last namevincent,john jr |
 |
|
akpaga
Constraint Violating Yak Guru
331 Posts |
Posted - 2008-08-19 : 13:57:43
|
quote: Originally posted by visakh16 you told no comma but what you've given contains comma b/w first & last namevincent,john jr
sorry typo . there is no comma between firstname and suffix. |
 |
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2008-08-19 : 15:03:34
|
quote: Originally posted by akpagaI forgot ,the a.cust_fullname also have the suffixes stored such as mr,jr.the order is lastname,firstname suffix(no comma between firstname and lastname). Can i ignore the suffix and compare only on lastname and firstname.
Apparently you did not even bother looking at my code. Never mind, then.Boycott Beijing Olympics 2008 |
 |
|
akpaga
Constraint Violating Yak Guru
331 Posts |
Posted - 2008-08-19 : 15:27:20
|
quote: Originally posted by blindman
quote: Originally posted by akpagaI forgot ,the a.cust_fullname also have the suffixes stored such as mr,jr.the order is lastname,firstname suffix(no comma between firstname and lastname). Can i ignore the suffix and compare only on lastname and firstname.
Apparently you did not even bother looking at my code. Never mind, then.Boycott Beijing Olympics 2008
No,the link you provide is not opening in my browser. thats the reason. |
 |
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2008-08-19 : 16:50:03
|
quote: Originally posted by akpagaNo,the link you provide is not opening in my browser. thats the reason.
Well that ain't no good. Why didn't you say so?Here is the function:create function FormatName(@NameString varchar(100), @NameFormat varchar(20))returns varchar(100) asbegin--blindman, 11/04--blindman, 7/06: modified to account for suffixes trailing last name. Added to honorific/suffix list.--blindman, 6/17/08: Added "CPA" suffix and checks for non-alpha characters and "EXT" string.--blindman, 6/18/2008: Added support for poorly hyphenated names and suffixes preceded by commas.--FormatName decodes a NameString into its component parts and returns it in a requested format.--@NameString is the raw value to be parsed.--@NameFormat is a string that defines the output format. Each letter in the string represents--a component of the name in the order that it is to be returned.-- [H] = Full honorific-- [h] = Abbreviated honorific-- [F] = First name-- [f] = First initial-- [M] = Middle name-- [m] = Middle initial-- [L] = Last name-- [l] = Last initial-- [S] = Full suffix-- [s] = Abbreviated suffix-- [.] = Period-- [,] = Comma-- [ ] = Space--Test variables-- declare @NameString varchar(50)-- declare @NameFormat varchar(20)-- set @NameFormat = 'F M L S'-- set @NameString = 'Melvin Carter, Jr'Declare @Honorific varchar(20)Declare @FirstName varchar(20)Declare @MiddleName varchar(30)Declare @LastName varchar(30)Declare @Suffix varchar(20)Declare @TempString varchar(100)Declare @TempString2 varchar(100)Declare @IgnorePeriod char(1)--Prepare the string--Make sure each period is followed by a space character.set @NameString = rtrim(ltrim(replace(@NameString, '.', '. ')))--Remove disallowed charactersdeclare @PatternString varchar(50)set @PatternString = '%[^a-z ,-]%'while patindex(@PatternString, @NameString) > 0 set @NameString = stuff(@NameString, patindex(@PatternString, @NameString), 1, ' ')--Remove telephone extset @NameString = ltrim(rtrim(replace(' ' + @NameString + ' ', ' EXT ', ' ')))--Eliminate double-spaces.while charindex(' ', @NameString) > 0 set @NameString = replace(@NameString, ' ', ' ')--Eliminate periodswhile charindex('.', @NameString) > 0 set @NameString = replace(@NameString, '.', '')--Remove spaces around hyphenated namesset @NameString = replace(replace(@NameString, '- ', '-'), ' -', '-')--Remove commas before suffixesset @NameString = replace(@NameString, ', Jr', ' Jr')set @NameString = replace(@NameString, ', Sr', ' Sr')set @NameString = replace(@NameString, ', II', ' II')set @NameString = replace(@NameString, ', III', ' III')--Temporarily join multi-word surnamesset @NameString = ltrim(replace(' ' + @NameString, ' Del ', ' Del~'))set @NameString = ltrim(replace(' ' + @NameString, ' Van ', ' Van~'))set @NameString = ltrim(replace(' ' + @NameString, ' Von ', ' Von~'))set @NameString = ltrim(replace(' ' + @NameString, ' Mc ', ' Mc~'))set @NameString = ltrim(replace(' ' + @NameString, ' Mac ', ' Mac~'))set @NameString = ltrim(replace(' ' + @NameString, ' La ', ' La~')) --Must be checked before "De", to handle "De La [Surname]"s.set @NameString = ltrim(replace(' ' + @NameString, ' De ', ' De~'))--If the lastname is listed first, strip it off.set @TempString = rtrim(left(@NameString, charindex(' ', @NameString)))--Below logic now handled by joining multi-word surnames above.--if @TempString in ('VAN', 'VON', 'MC', 'Mac', 'DE') set @TempString = rtrim(left(@NameString, charindex(' ', @NameString, len(@TempString)+2)))--Search for suffixes trailing the LastNameset @TempString2 = ltrim(right(@NameString, len(@NameString) - len(@TempString)))set @TempString2 = rtrim(left(@TempString2, charindex(' ', @TempString2)))if right(@TempString2, 1) = ',' begin set @Suffix = left(@TempString2, len(@TempString2)-1) set @LastName = left(@TempString, len(@TempString)) endif right(@TempString, 1) = ',' set @LastName = left(@TempString, len(@TempString)-1)if len(@LastName) > 0 set @NameString = ltrim(right(@NameString, len(@NameString) - len(@TempString)))if len(@Suffix) > 0 set @NameString = ltrim(right(@NameString, len(@NameString) - len(@TempString2)))--Get rid of any remaining commaswhile charindex(',', @NameString) > 0 set @NameString = replace(@NameString, ',', '')--Get Honorific and strip it out of the stringset @TempString = rtrim(left(@NameString, charindex(' ', @NameString + ' ')))if @TempString in ( 'Admiral', 'Adm', 'Captain', 'Cpt', 'Capt', 'Commander', 'Cmd', 'Corporal', 'Cpl', 'Doctor', 'Dr', 'Father', 'Fr', 'General', 'Gen', 'Governor', 'Gov', 'Honorable', 'Hon', 'Lieutenant', 'Lt', 'Madam', 'Mdm', 'Madame', 'Mme', 'Mademoiselle', 'Mlle', 'Major', 'Maj', 'Miss', 'Ms', 'Mr', 'Mrs', 'President', 'Pres', 'Private', 'Pvt', 'Professor', 'Prof', 'Rabbi', 'Reverend', 'Rev', 'Senior', 'Sr', 'Seniora', 'Sra', 'Seniorita', 'Srta', 'Sergeant', 'Sgt', 'Sir', 'Sister') set @Honorific = @TempStringif len(@Honorific) > 0 set @NameString = ltrim(right(@NameString, len(@NameString) - len(@TempString)))--Get Suffix and strip it out of the stringif @Suffix is null begin set @TempString = ltrim(right(@NameString, charindex(' ', Reverse(@NameString) + ' '))) if @TempString in ( 'Attorney', 'Att', 'Atty', 'BA', 'BS', 'CPA', 'DDS', 'DVM', 'Esquire', 'Esq', 'II', 'III', 'IV', 'Junior', 'Jr', 'MBA', 'MD', 'OD', 'PHD', 'Senior', 'Sr') set @Suffix = @TempString if len(@Suffix) > 0 set @NameString = rtrim(left(@NameString, len(@NameString) - len(@TempString))) endif @LastName is nullbegin --Get LastName and strip it out of the string set @LastName = ltrim(right(@NameString, charindex(' ', Reverse(@NameString) + ' '))) set @NameString = rtrim(left(@NameString, len(@NameString) - len(@LastName)))--Below logic now handled by joining multi-word surnames above./* --Check to see if the last name has two parts set @TempString = ltrim(right(@NameString, charindex(' ', Reverse(@NameString) + ' '))) if @TempString in ('VAN', 'VON', 'MC', 'Mac', 'DE') begin set @LastName = @TempString + ' ' + @LastName set @NameString = rtrim(left(@NameString, len(@NameString) - len(@TempString))) end*/end--Get FirstName and strip it out of the stringset @FirstName = rtrim(left(@NameString, charindex(' ', @NameString + ' ')))set @NameString = ltrim(right(@NameString, len(@NameString) - len(@FirstName)))--Anything remaining is MiddleNameset @MiddleName = @NameString--Create the output stringset @TempString = ''while len(@NameFormat) > 0begin if @IgnorePeriod = 'F' or left(@NameFormat, 1) <> '.' begin set @IgnorePeriod = 'F' set @TempString = @TempString + case ascii(left(@NameFormat, 1)) when '32' then case right(@TempString, 1) when ' ' then '' else ' ' end when '44' then case right(@TempString, 1) when ' ' then '' else ',' end when '46' then case right(@TempString, 1) when ' ' then '' else '.' end when '70' then isnull(@FirstName, '') when '72' then case @Honorific when 'Adm' then 'Admiral' when 'Capt' then 'Captain' when 'Cmd' then 'Commander' when 'Cpl' then 'Corporal' when 'Cpt' then 'Captain' when 'Dr' then 'Doctor' when 'Fr' then 'Father' when 'Gen' then 'General' when 'Gov' then 'Governor' when 'Hon' then 'Honorable' when 'Lt' then 'Lieutenant' when 'Maj' then 'Major' when 'Mdm' then 'Madam' when 'Mlle' then 'Mademoiselle' when 'Mme' then 'Madame' when 'Ms' then 'Miss' when 'Pres' then 'President' when 'Prof' then 'Professor' when 'Pvt' then 'Private' when 'Sr' then 'Senior' when 'Sra' then 'Seniora' when 'Srta' then 'Seniorita' when 'Rev' then 'Reverend' when 'Sgt' then 'Sergeant' else isnull(@Honorific, '') end when '76' then isnull(@LastName, '') when '77' then isnull(@MiddleName, '') when '83' then case @Suffix when 'Att' then 'Attorney' when 'Atty' then 'Attorney' when 'Esq' then 'Esquire' when 'Jr' then 'Junior' when 'Sr' then 'Senior' else isnull(@Suffix, '') end when '102' then isnull(left(@FirstName, 1), '') when '104' then case @Honorific when 'Admiral' then 'Adm' when 'Captain' then 'Capt' when 'Commander' then 'Cmd' when 'Corporal' then 'Cpl' when 'Doctor' then 'Dr' when 'Father' then 'Fr' when 'General' then 'Gen' when 'Governor' then 'Gov' when 'Honorable' then 'Hon' when 'Lieutenant' then 'Lt' when 'Madam' then 'Mdm' when 'Madame' then 'Mme' when 'Mademoiselle' then 'Mlle' when 'Major' then 'Maj' when 'Miss' then 'Ms' when 'President' then 'Pres' when 'Private' then 'Pvt' when 'Professor' then 'Prof' when 'Reverend' then 'Rev' when 'Senior' then 'Sr' when 'Seniora' then 'Sra' when 'Seniorita' then 'Srta' when 'Sergeant' then 'Sgt' else isnull(@Honorific, '') end when '108' then isnull(left(@LastName, 1), '') when '109' then isnull(left(@MiddleName, 1), '') when '115' then case @Suffix when 'Attorney' then 'Atty' when 'Esquire' then 'Esq' when 'Junior' then 'Jr' when 'Senior' then 'Sr' else isnull(@Suffix, '') end else '' end --The following honorifics and suffixes have no further abbreviations, and so should not be followed by a period: if ((ascii(left(@NameFormat, 1)) = 72 and @Honorific in ('Rabbi', 'Sister')) or (ascii(left(@NameFormat, 1)) = 115 and @Suffix in ('BA', 'BS', 'DDS', 'DVM', 'II', 'III', 'IV', 'V', 'MBA', 'MD', 'PHD'))) set @IgnorePeriod = 'T' end set @NameFormat = right(@NameFormat, len(@NameFormat) - 1)end--select replace(@TempString, '~', ' ')Return replace(@TempString, '~', ' ')end Boycott Beijing Olympics 2008 |
 |
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2008-08-19 : 17:47:36
|
quote: What i want is to compare both the tables on the name field
Please don't miff, but what you should want is to reengineer this horrible database structure.WebfredPlanning replaces chance by mistake |
 |
|
akpaga
Constraint Violating Yak Guru
331 Posts |
Posted - 2008-08-20 : 09:47:14
|
quote: Originally posted by blindman
quote: Originally posted by akpagaNo,the link you provide is not opening in my browser. thats the reason.
Well that ain't no good. Why didn't you say so?Here is the function:create function FormatName(@NameString varchar(100), @NameFormat varchar(20))returns varchar(100) asbegin--blindman, 11/04--blindman, 7/06: modified to account for suffixes trailing last name. Added to honorific/suffix list.--blindman, 6/17/08: Added "CPA" suffix and checks for non-alpha characters and "EXT" string.--blindman, 6/18/2008: Added support for poorly hyphenated names and suffixes preceded by commas.--FormatName decodes a NameString into its component parts and returns it in a requested format.--@NameString is the raw value to be parsed.--@NameFormat is a string that defines the output format. Each letter in the string represents--a component of the name in the order that it is to be returned.-- [H] = Full honorific-- [h] = Abbreviated honorific-- [F] = First name-- [f] = First initial-- [M] = Middle name-- [m] = Middle initial-- [L] = Last name-- [l] = Last initial-- [S] = Full suffix-- [s] = Abbreviated suffix-- [.] = Period-- [,] = Comma-- [ ] = Space--Test variables-- declare @NameString varchar(50)-- declare @NameFormat varchar(20)-- set @NameFormat = 'F M L S'-- set @NameString = 'Melvin Carter, Jr'Declare @Honorific varchar(20)Declare @FirstName varchar(20)Declare @MiddleName varchar(30)Declare @LastName varchar(30)Declare @Suffix varchar(20)Declare @TempString varchar(100)Declare @TempString2 varchar(100)Declare @IgnorePeriod char(1)--Prepare the string--Make sure each period is followed by a space character.set @NameString = rtrim(ltrim(replace(@NameString, '.', '. ')))--Remove disallowed charactersdeclare @PatternString varchar(50)set @PatternString = '%[^a-z ,-]%'while patindex(@PatternString, @NameString) > 0 set @NameString = stuff(@NameString, patindex(@PatternString, @NameString), 1, ' ')--Remove telephone extset @NameString = ltrim(rtrim(replace(' ' + @NameString + ' ', ' EXT ', ' ')))--Eliminate double-spaces.while charindex(' ', @NameString) > 0 set @NameString = replace(@NameString, ' ', ' ')--Eliminate periodswhile charindex('.', @NameString) > 0 set @NameString = replace(@NameString, '.', '')--Remove spaces around hyphenated namesset @NameString = replace(replace(@NameString, '- ', '-'), ' -', '-')--Remove commas before suffixesset @NameString = replace(@NameString, ', Jr', ' Jr')set @NameString = replace(@NameString, ', Sr', ' Sr')set @NameString = replace(@NameString, ', II', ' II')set @NameString = replace(@NameString, ', III', ' III')--Temporarily join multi-word surnamesset @NameString = ltrim(replace(' ' + @NameString, ' Del ', ' Del~'))set @NameString = ltrim(replace(' ' + @NameString, ' Van ', ' Van~'))set @NameString = ltrim(replace(' ' + @NameString, ' Von ', ' Von~'))set @NameString = ltrim(replace(' ' + @NameString, ' Mc ', ' Mc~'))set @NameString = ltrim(replace(' ' + @NameString, ' Mac ', ' Mac~'))set @NameString = ltrim(replace(' ' + @NameString, ' La ', ' La~')) --Must be checked before "De", to handle "De La [Surname]"s.set @NameString = ltrim(replace(' ' + @NameString, ' De ', ' De~'))--If the lastname is listed first, strip it off.set @TempString = rtrim(left(@NameString, charindex(' ', @NameString)))--Below logic now handled by joining multi-word surnames above.--if @TempString in ('VAN', 'VON', 'MC', 'Mac', 'DE') set @TempString = rtrim(left(@NameString, charindex(' ', @NameString, len(@TempString)+2)))--Search for suffixes trailing the LastNameset @TempString2 = ltrim(right(@NameString, len(@NameString) - len(@TempString)))set @TempString2 = rtrim(left(@TempString2, charindex(' ', @TempString2)))if right(@TempString2, 1) = ',' begin set @Suffix = left(@TempString2, len(@TempString2)-1) set @LastName = left(@TempString, len(@TempString)) endif right(@TempString, 1) = ',' set @LastName = left(@TempString, len(@TempString)-1)if len(@LastName) > 0 set @NameString = ltrim(right(@NameString, len(@NameString) - len(@TempString)))if len(@Suffix) > 0 set @NameString = ltrim(right(@NameString, len(@NameString) - len(@TempString2)))--Get rid of any remaining commaswhile charindex(',', @NameString) > 0 set @NameString = replace(@NameString, ',', '')--Get Honorific and strip it out of the stringset @TempString = rtrim(left(@NameString, charindex(' ', @NameString + ' ')))if @TempString in ( 'Admiral', 'Adm', 'Captain', 'Cpt', 'Capt', 'Commander', 'Cmd', 'Corporal', 'Cpl', 'Doctor', 'Dr', 'Father', 'Fr', 'General', 'Gen', 'Governor', 'Gov', 'Honorable', 'Hon', 'Lieutenant', 'Lt', 'Madam', 'Mdm', 'Madame', 'Mme', 'Mademoiselle', 'Mlle', 'Major', 'Maj', 'Miss', 'Ms', 'Mr', 'Mrs', 'President', 'Pres', 'Private', 'Pvt', 'Professor', 'Prof', 'Rabbi', 'Reverend', 'Rev', 'Senior', 'Sr', 'Seniora', 'Sra', 'Seniorita', 'Srta', 'Sergeant', 'Sgt', 'Sir', 'Sister') set @Honorific = @TempStringif len(@Honorific) > 0 set @NameString = ltrim(right(@NameString, len(@NameString) - len(@TempString)))--Get Suffix and strip it out of the stringif @Suffix is null begin set @TempString = ltrim(right(@NameString, charindex(' ', Reverse(@NameString) + ' '))) if @TempString in ( 'Attorney', 'Att', 'Atty', 'BA', 'BS', 'CPA', 'DDS', 'DVM', 'Esquire', 'Esq', 'II', 'III', 'IV', 'Junior', 'Jr', 'MBA', 'MD', 'OD', 'PHD', 'Senior', 'Sr') set @Suffix = @TempString if len(@Suffix) > 0 set @NameString = rtrim(left(@NameString, len(@NameString) - len(@TempString))) endif @LastName is nullbegin --Get LastName and strip it out of the string set @LastName = ltrim(right(@NameString, charindex(' ', Reverse(@NameString) + ' '))) set @NameString = rtrim(left(@NameString, len(@NameString) - len(@LastName)))--Below logic now handled by joining multi-word surnames above./* --Check to see if the last name has two parts set @TempString = ltrim(right(@NameString, charindex(' ', Reverse(@NameString) + ' '))) if @TempString in ('VAN', 'VON', 'MC', 'Mac', 'DE') begin set @LastName = @TempString + ' ' + @LastName set @NameString = rtrim(left(@NameString, len(@NameString) - len(@TempString))) end*/end--Get FirstName and strip it out of the stringset @FirstName = rtrim(left(@NameString, charindex(' ', @NameString + ' ')))set @NameString = ltrim(right(@NameString, len(@NameString) - len(@FirstName)))--Anything remaining is MiddleNameset @MiddleName = @NameString--Create the output stringset @TempString = ''while len(@NameFormat) > 0begin if @IgnorePeriod = 'F' or left(@NameFormat, 1) <> '.' begin set @IgnorePeriod = 'F' set @TempString = @TempString + case ascii(left(@NameFormat, 1)) when '32' then case right(@TempString, 1) when ' ' then '' else ' ' end when '44' then case right(@TempString, 1) when ' ' then '' else ',' end when '46' then case right(@TempString, 1) when ' ' then '' else '.' end when '70' then isnull(@FirstName, '') when '72' then case @Honorific when 'Adm' then 'Admiral' when 'Capt' then 'Captain' when 'Cmd' then 'Commander' when 'Cpl' then 'Corporal' when 'Cpt' then 'Captain' when 'Dr' then 'Doctor' when 'Fr' then 'Father' when 'Gen' then 'General' when 'Gov' then 'Governor' when 'Hon' then 'Honorable' when 'Lt' then 'Lieutenant' when 'Maj' then 'Major' when 'Mdm' then 'Madam' when 'Mlle' then 'Mademoiselle' when 'Mme' then 'Madame' when 'Ms' then 'Miss' when 'Pres' then 'President' when 'Prof' then 'Professor' when 'Pvt' then 'Private' when 'Sr' then 'Senior' when 'Sra' then 'Seniora' when 'Srta' then 'Seniorita' when 'Rev' then 'Reverend' when 'Sgt' then 'Sergeant' else isnull(@Honorific, '') end when '76' then isnull(@LastName, '') when '77' then isnull(@MiddleName, '') when '83' then case @Suffix when 'Att' then 'Attorney' when 'Atty' then 'Attorney' when 'Esq' then 'Esquire' when 'Jr' then 'Junior' when 'Sr' then 'Senior' else isnull(@Suffix, '') end when '102' then isnull(left(@FirstName, 1), '') when '104' then case @Honorific when 'Admiral' then 'Adm' when 'Captain' then 'Capt' when 'Commander' then 'Cmd' when 'Corporal' then 'Cpl' when 'Doctor' then 'Dr' when 'Father' then 'Fr' when 'General' then 'Gen' when 'Governor' then 'Gov' when 'Honorable' then 'Hon' when 'Lieutenant' then 'Lt' when 'Madam' then 'Mdm' when 'Madame' then 'Mme' when 'Mademoiselle' then 'Mlle' when 'Major' then 'Maj' when 'Miss' then 'Ms' when 'President' then 'Pres' when 'Private' then 'Pvt' when 'Professor' then 'Prof' when 'Reverend' then 'Rev' when 'Senior' then 'Sr' when 'Seniora' then 'Sra' when 'Seniorita' then 'Srta' when 'Sergeant' then 'Sgt' else isnull(@Honorific, '') end when '108' then isnull(left(@LastName, 1), '') when '109' then isnull(left(@MiddleName, 1), '') when '115' then case @Suffix when 'Attorney' then 'Atty' when 'Esquire' then 'Esq' when 'Junior' then 'Jr' when 'Senior' then 'Sr' else isnull(@Suffix, '') end else '' end --The following honorifics and suffixes have no further abbreviations, and so should not be followed by a period: if ((ascii(left(@NameFormat, 1)) = 72 and @Honorific in ('Rabbi', 'Sister')) or (ascii(left(@NameFormat, 1)) = 115 and @Suffix in ('BA', 'BS', 'DDS', 'DVM', 'II', 'III', 'IV', 'V', 'MBA', 'MD', 'PHD'))) set @IgnorePeriod = 'T' end set @NameFormat = right(@NameFormat, len(@NameFormat) - 1)end--select replace(@TempString, '~', ' ')Return replace(@TempString, '~', ' ')end Boycott Beijing Olympics 2008
How should i use this function for my application? Thanks |
 |
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2008-08-20 : 10:06:38
|
To return the first name:select dbo.FormatName([YourFullNameString], 'F')To return the last name:select dbo.FormatName([YourFullNameString], 'L')Boycott Beijing Olympics 2008 |
 |
|
akpaga
Constraint Violating Yak Guru
331 Posts |
Posted - 2008-08-20 : 16:03:20
|
quote: Originally posted by blindman To return the first name:select dbo.FormatName([YourFullNameString], 'F')To return the last name:select dbo.FormatName([YourFullNameString], 'L')Boycott Beijing Olympics 2008
thanks for you response. it worked. |
 |
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2008-08-20 : 16:31:29
|
Of course it worked. I wrote it.But you are welcome!Boycott Beijing Olympics 2008 |
 |
|
|
|
|
|
|