#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    1
    Rep Power
    0

    User defined functions for proper case


    I am trying to write a function that takes as its input, a proper name and returns the name in proper case. For example, I type in JON DOE and it returns Jon Doe, or jon doe and returns Jon Doe. Any help would be greatly appreciated.
  2. #2
  3. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2003
    Location
    KC
    Posts
    6
    Rep Power
    0
    Try a google.com search using "sql proper name". The first site found is "SQL Server Examples" by Greg Larson. See http://www.geocities.com/sqlserverexamples/string6.htm
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2003
    Location
    KC
    Posts
    6
    Rep Power
    0
    This scriipt can be changed to a function:

    create table #temp (
    name varchar(100))
    insert into #temp values('GREGORY ALAN LARSEN')
    insert into #temp values('TODD JOSHUA SMITH')
    select
    -- Get First Character of First Name
    upper(substring(name,1,1)) +
    -- Get rest of first name
    lower(substring(name,2,charindex(' ',name)-2)) + ' ' +
    -- Get first Character of Middle Name
    upper(substring(name,charindex(' ',name)+1,1)) +
    -- Get rest of Middle Name
    lower(substring(substring(name,charindex(' ',name)+2,len(name)),1,
    charindex(' ',substring(name,charindex(' ',name)+2,len(name)))-1)) + ' ' +
    -- Get First Character to Last Name
    upper(substring(substring(name,charindex(' ',name)+2,len(name)),
    charindex(' ',substring(name,charindex(' ',name)+2,len(name)))+1,1)) +
    -- Get Rest of Last Name
    lower(substring(substring(name,charindex(' ',name)+2,len(name)),
    charindex(' ',substring(name,charindex(' ',name)+2,len(name)))+2,
    len(substring(name,charindex(' ',name)+2,len(name)))))
    from #temp
    drop table #temp
  6. #4
  7. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2003
    Location
    KC
    Posts
    6
    Rep Power
    0
    Greg Larson's comments:
    "This script will proper case the first, middle, and last name for a person. This script strips apart the first, middle and last name then uppercase the first character of each name, and lowercases the rest. This script only works if there is a single space between each names. This script uses the charindex function to identify the character offset of the space between each name. If multiple spaces exist they will need to be removed first (look at "remove white space between multiple words")."
  8. #5
  9. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2003
    Location
    KC
    Posts
    6
    Rep Power
    0
    Greg's routine to remove white space between multiiple words can be changed to locate the first white space and remove remaining white space rather than remove ALL white space.

IMN logo majestic logo threadwatch logo seochat tools logo