RSS
 

Posts Tagged ‘sql function’

SQL Format Name Function

10 Dec

I was working for an ASP (application service provider) awhile back and got sick of formatting our members names in code at the time of rendering the page.

I asked our DBA for advice if he thought it would be better performance to simply do this in the SQL calls to the database, of course his answer was yes, however what he suggested was to simply concatenate the name parts into an alias field.

Needless to say, it ended up bombing the page out around 1000 records.

After searching online for a few hours I came across a SQL Function that does exactly what was needed, and WHAMMMOOO!!!   No more page bombing.

Here it is, with usage to share with all (because it took so long to find it)

  1. SET ANSI_NULLS ON
  2. SET QUOTED_IDENTIFIER ON
  3. GO
  4. ALTER FUNCTION [dbo].[FormatName](@NameString varchar(100), @NameFormat varchar(20))
  5. returns varchar(100) AS
  6. begin
  7. –FormatName parses a NameString into its component parts and returns it in a requested format.
  8. –
  9. –@NameString is the raw value to be parsed.
  10. –@NameFormat is a string that defines the output format.  Each letter in the string represents
  11. –a component of the name in the order that it is to be returned.
  12. –    [H] = Full honorific
  13. –    [h] = Abbreviated honorific
  14. –    [F] = First name
  15. –    [f] = First initial
  16. –    [M] = Middle name
  17. –    [m] = Middle initial
  18. –    [L] = Last name
  19. –    [l] = Last initial
  20. –    [S] = Full suffix
  21. –    [s] = Abbreviated suffix
  22. –    [.] = Period
  23. –    [,] = Comma
  24. –    [ ] = Space
  25.  
  26. –Example: select dbo.Formatname(‘Reverend Gregory Robert Von Finzer Junior’, ‘L, h. F m. s.’)
  27. –Result: ‘Von Finzer, Rev. Gregory R. Jr.’
  28.  
  29. –Test variables
  30. – declare    @NameString varchar(50)
  31. – declare    @NameFormat varchar(20)
  32. – set    @NameFormat = ‘L, h. F m. s.’
  33. – set    @NameString = ‘Reverend Gregory Robert Von Finzer Junior’
  34.  
  35. Declare    @Honorific varchar(20)
  36. Declare    @FirstName varchar(20)
  37. Declare    @MiddleName varchar(30)
  38. Declare    @LastName varchar(30)
  39. Declare    @Suffix varchar(20)
  40. Declare    @TempString varchar(100)
  41. Declare    @IgnorePeriod char(1)
  42.  
  43. –Prepare the string
  44. –Make sure each period is followed by a space character.
  45. SET    @NameString = rtrim(ltrim(REPLACE(@NameString, ‘.’, ‘. ‘)))
  46. –Eliminate double-spaces.
  47. while  charindex(‘  ‘, @NameString) > 0 SET @NameString = REPLACE(@NameString, ‘  ‘, ‘ ‘)
  48. –Eliminate periods
  49. while  charindex(‘.’, @NameString) > 0 SET @NameString = REPLACE(@NameString, ‘.’, ”)
  50.  
  51. –If the lastname is listed first, strip it off.
  52. SET    @TempString = rtrim(LEFT(@NameString, charindex(‘ ‘, @NameString)))
  53. IF    @TempString IN (‘VAN’, ‘VON’, ‘MC’, ‘Mac’, ‘DE’) SET @TempString = rtrim(LEFT(@NameString, charindex(‘ ‘, @NameString, len(@TempString)+2)))
  54. IF    RIGHT(@TempString, 1) = ‘,’ SET @LastName = LEFT(@TempString, len(@TempString)-1)
  55. IF    len(@LastName) > 0 SET    @NameString = ltrim(RIGHT(@NameString, len(@NameString) - len(@TempString)))
  56.  
  57. –Get rid of any remaining commas
  58. while  charindex(‘,’, @NameString) > 0 SET @NameString = REPLACE(@NameString, ‘,’, ”)
  59.  
  60. –Get Honorific and strip it out of the string
  61. SET    @TempString = rtrim(LEFT(@NameString, charindex(‘ ‘, @NameString + ‘ ‘)))
  62. IF    @TempString IN (‘MR’, ‘MRS’, ‘MS’, ‘DR’, ‘Doctor’, ‘REV’, ‘Reverend’, ‘SIR’, ‘HON’, ‘Honorable’, ‘CPL’, ‘Corporal’, ‘SGT’, ‘Sergeant’, ‘GEN’, ‘General’, ‘CMD’, ‘Commander’,  ‘CPT’, ‘CAPT’, ‘Captain’, ‘MAJ’, ‘Major’, ‘PVT’, ‘Private’, ‘LT’, ‘Lieutenant’, ‘FATHER’, ‘SISTER’) SET @Honorific = @TempString
  63. IF    len(@Honorific) > 0 SET    @NameString = ltrim(RIGHT(@NameString, len(@NameString) - len(@TempString)))
  64.  
  65. –Get Suffix and strip it out of the string
  66. SET    @TempString = ltrim(RIGHT(@NameString, charindex(‘ ‘, Reverse(@NameString) + ‘ ‘)))
  67. IF    @TempString IN (‘Jr’, ‘Sr’,  ‘II’, ‘III’, ‘Esq’, ‘Junior’, ‘Senior’) SET @Suffix = @TempString
  68. IF    len(@Suffix) > 0 SET @NameString = rtrim(LEFT(@NameString, len(@NameString) - len(@TempString)))
  69.  
  70. IF @LastName IS NULL
  71. begin
  72. –Get LastName and strip it out of the string
  73. SET    @LastName = ltrim(RIGHT(@NameString, charindex(‘ ‘, Reverse(@NameString) + ‘ ‘)))
  74. SET    @NameString = rtrim(LEFT(@NameString, len(@NameString) - len(@LastName)))
  75. –Check to see if the last name has two parts
  76. SET    @TempString = ltrim(RIGHT(@NameString, charindex(‘ ‘, Reverse(@NameString) + ‘ ‘)))
  77. IF    @TempString IN (‘VAN’, ‘VON’, ‘MC’, ‘Mac’, ‘DE’)
  78. begin
  79. SET @LastName = @TempString + ‘ ‘ + @LastName
  80. SET @NameString = rtrim(LEFT(@NameString, len(@NameString) - len(@TempString)))
  81. end
  82. end
  83.  
  84. –Get FirstName and strip it out of the string
  85. SET    @FirstName = rtrim(LEFT(@NameString, charindex(‘ ‘, @NameString + ‘ ‘)))
  86. SET    @NameString = ltrim(RIGHT(@NameString, len(@NameString) - len(@FirstName)))
  87.  
  88. –Anything remaining is MiddleName
  89. SET    @MiddleName = @NameString
  90.  
  91. –Create the output string
  92. SET    @TempString = ”
  93. while len(@NameFormat) > 0
  94. begin
  95. IF @IgnorePeriod = ‘F’ OR LEFT(@NameFormat, 1) <> ‘.’
  96. begin
  97. SET @IgnorePeriod = ‘F’
  98. SET @TempString = @TempString +
  99. case ascii(LEFT(@NameFormat, 1))
  100. when ’72′ then case @Honorific
  101. when ‘Dr’ then ‘Doctor’
  102. when ‘Rev’ then ‘Reverend’
  103. when ‘Hon’ then ‘Honorable’
  104. when ‘Maj’ then ‘Major’
  105. when ‘Pvt’ then ‘Private’
  106. when ‘Lt’ then ‘Lieutenant’
  107. when ‘Capt’ then ‘Captain’
  108. when ‘Cpt’ then ‘Captain’
  109. when ‘Cmd’ then ‘Commander’
  110. when ‘Gen’ then ‘General’
  111. when ‘Sgt’ then ‘Sergeant’
  112. when ‘Cpl’ then ‘Corporal’
  113. else isnull(@Honorific, ”)
  114. end
  115. when ’70′ then isnull(@FirstName, ”)
  116. when ’77′ then isnull(@MiddleName, ”)
  117. when ’76′ then isnull(@LastName, ”)
  118. when ’83′ then case @Suffix
  119. when ‘Jr’ then ‘Junior’
  120. when ‘Sr’ then ‘Senior’
  121. when ‘Esq’ then ‘Esquire’
  122. else isnull(@Suffix, ”)
  123. end
  124. when ’104′ then case @Honorific
  125. when ‘Doctor’ then ‘Dr’
  126. when ‘Reverend’ then ‘Rev’
  127. when ‘Honorable’ then ‘Hon’
  128. when ‘Major’ then ‘Maj’
  129. when ‘Private’ then ‘Pvt’
  130. when ‘Lieutenant’ then ‘Lt’
  131. when ‘Captain’ then ‘Capt’
  132. when ‘Cpt’ then ‘Capt’
  133. when ‘Commander’ then ‘Cmd’
  134. when ‘General’ then ‘Gen’
  135. when ‘Sergeant’ then ‘Sgt’
  136. when ‘Corporal’ then ‘Cpl’
  137. else isnull(@Honorific, ”)
  138. end
  139. when ’102′ then isnull(LEFT(@FirstName, 1), ”)
  140. when ’109′ then isnull(LEFT(@MiddleName, 1), ”)
  141. when ’108′ then isnull(LEFT(@LastName, 1), ”)
  142. when ’115′ then case @Suffix
  143. when ‘Junior’ then ‘Jr’
  144. when ‘Senior’ then ‘Sr’
  145. when ‘Esquire’ then ‘Esq’
  146. else isnull(@Suffix, ”)
  147. end
  148. when ’46′ then case RIGHT(@TempString, 1)
  149. when ‘ ‘ then ”
  150. else ‘.’
  151. end
  152. when ’44′ then case RIGHT(@TempString, 1)
  153. when ‘ ‘ then ”
  154. else ‘,’
  155. end
  156. when ’32′ then case RIGHT(@TempString, 1)
  157. when ‘ ‘ then ”
  158. else ‘ ‘
  159. end
  160. else ”
  161. end
  162. IF ((ascii(LEFT(@NameFormat, 1)) = 72 AND @Honorific IN (‘FATHER’, ‘SISTER’))
  163. OR (ascii(LEFT(@NameFormat, 1)) = 115 AND @Suffix IN (‘II’, ‘III’)))
  164. SET @IgnorePeriod = ‘T’
  165. end
  166. SET @NameFormat = RIGHT(@NameFormat, len(@NameFormat) - 1)
  167. end
  168.  
  169. RETURN @TempString
  170. end
Link to this post!
 
No Comments

Posted in Web Design

 
 

Optimized by SEO Ultimate