Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2011
    Posts
    3

    Unanswered: Upper Case Names

    Hay Everybody I'm New the the SQL Forums and hope to have a wonderful experience with you guys in the future and hopefully for a while. For now i Have a scalar function that is called any string can get passed to it and will separate the name first middle last and put an upper case for the names but my problem is i need help with. If there is a name like o'neil from this function as of right now it would put O'neil unless you separate the word like O' Neil. I need to be able to put in any name and it put a capital letter after and end with O'Neil. Thank you for any help.

    IF @CurrentString IS NULL
    RETURN NULL

    if @CurrentString like '%<%' set @CurrentString = replace(@CurrentString,'<','')
    if @CurrentString like '%>%' set @CurrentString = replace(@CurrentString,'>','')

    DECLARE @str varchar(512),
    @word varchar(512),
    @final varchar(512),
    @begin int,
    @end int,
    @index int,
    @test varchar(30)

    SELECT @str = LTRIM(RTRIM(@CurrentString)), @final = ''

    WHILE LEN(LTRIM(RTRIM(@str))) > 0
    BEGIN
    SELECT @end = CHARINDEX(' ', @str, 0)

    IF @end > 0
    BEGIN
    SELECT @word = SUBSTRING(@str, 1, @end -1)
    SELECT @str = SUBSTRING(@str, @end + 1, LEN(@str) - @end)
    END
    ELSE
    BEGIN
    SELECT @word = @str, @str = ''
    END

    IF @word <> 'and'
    BEGIN

    IF LEN(@word) > 1
    BEGIN
    SELECT @word = UPPER(SUBSTRING(@word, 1, 1)) + LOWER(RIGHT(@word, LEN(@word) - 1) )
    END
    ELSE
    BEGIN
    SELECT @word = UPPER(@word)
    END

    SELECT @test = exceptiontext
    FROM Global_ProperCaseExceptions
    WHERE exceptiontext = @word

    IF @test IS NOT NULL
    BEGIN
    SELECT @word = @test
    SELECT @test = null
    END
    ELSE IF UPPER(@word) IN ('II', 'III', 'SW', 'SE', 'NW', 'NE')
    BEGIN
    SELECT @word = UPPER(@word)
    END
    ELSE IF LEFT(@word, 2) = 'Mc' AND LEN(@word) > 2
    BEGIN
    SELECT @word = LEFT(@word, 2) + UPPER(SUBSTRING(@word, 3, 1)) + RIGHT(@word, LEN(@word) - 3)
    END
    ELSE IF LEFT(@word, 3) = 'St.' AND LEN(@word) > 3
    BEGIN
    SELECT @word = LEFT(@word, 3) + UPPER(SUBSTRING(@word, 4, 1)) + RIGHT(@word, LEN(@word) - 4)
    END
    ELSE
    BEGIN
    SELECT @index = 1
    WHILE LEN(@word) > @index
    BEGIN
    IF SUBSTRING(@word, @index, 1) IN ('.')
    BEGIN
    SELECT @word = LEFT(@word, @index) +
    UPPER(SUBSTRING(@word, @index + 1, 1) ) +
    SUBSTRING(@word, @index + 2, LEN(@word) - (@index + 1) )
    END
    SELECT @index = @index + 1
    END
    END
    END
    SELECT @final = @final + @word + ' '
    END

    RETURN RTRIM(@final)
    END

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Try POSITION, that will get you to the location in the string where the ' exists, then get the next character and capitalize that one.

    Dave Nance

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Try my FORMATNAME function, which will parse most western style names and allow you to reassemble them according to a format mask:
    http://dl.dropbox.com/u/2507186/Func...FormatName.sql
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Sorry, that function does a good job of parsing names, but does not capitalize them.

    Try this function for capitalizing the names after they are split. I just added some code to support your example.
    http://dl.dropbox.com/u/2507186/Functions/ProperCap.sql
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Jul 2011
    Posts
    3

    Thank You

    Thank you. Thank you. That does help but what about this

    if @word like '%''%'
    begin
    select @word = SUBSTRING(@word, 1, CHARINDEX('''', @word) )
    + UPPER(SUBSTRING(@word, CHARINDEX('''', @word) + 1, 1))
    + SUBSTRING(@word, CHARINDEX('''', @word) + 2, 50)
    end

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...and if @word contains two apostrophies?
    You have to perform this check in a loop, as in my code.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •