Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Posts
    42

    Unanswered: Converting Field values with ALL CAPS to Inital Caps???

    I have found a stored proceedure that will convert a string from any format to initial caps followed by lower case letters. However, I'm such a newbie to SQL Server that I have no idea how to run the SP on a table.

    Here is the stored proceedure: http://www.planet-source-code.com/vb...d=581&lngWId=5

    How would I get this to run within a query to update a field?

    For example, I have a field called 'name' with records like:
    3M <- should remain '3M'
    123 INTERIORS <--- should be '123 Interiors'
    ALLIED SIGNAL <-- should be 'Allied Signal'

  2. #2
    Join Date
    Jun 2004
    Posts
    2

    Upper_first

    Create this function.
    Code:
    CREATE FUNCTION UPPER_FIRST (@StrIn VARCHAR(8000))
    RETURNS VARCHAR(8000)
    AS
    BEGIN
      RETURN(UPPER(SUBSTRING(@StrIn, 1, 1)) + SUBSTRING(@StrIn, 2, 8000))
    END
    Then you can update fields by doing something like this
    Code:
    UPDATE TABLENAME SET FIELDNAME = DBO.UPPERFIRST(FIELDNAME)
    Last edited by bling; 06-18-04 at 15:02.

  3. #3
    Join Date
    Feb 2004
    Posts
    42
    Quote Originally Posted by bling
    Create this function.
    Code:
    CREATE FUNCTION UPPER_FIRST (@StrIn VARCHAR(8000))
    RETURNS VARCHAR(8000)
    AS
    BEGIN
      RETURN(UPPER(SUBSTRING(@StrIn, 1, 1)) + SUBSTRING(@StrIn, 2, 8000))
    END
    Then you can update fields by doing something like this
    Code:
    UPDATE TABLENAME SET FIELDNAME = DBO.UPPERFIRST(FIELDNAME)
    Your solution would only initial cap the first word, not the entire string. So, 'ALLIED SIGNAL' would be 'Allied SIGNAL', not 'Allied Signal', thus why I want to use the stored proceedure that I linked to in my original post since it would Initial Cap every word in a string.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Might I suggest using:
    Code:
    --  ptp  20040618  Capitalize the first letter of each "word"
    
    CREATE FUNCTION dbo.fCapFirst(@pcIn NVARCHAR(4000))
    RETURNS NVARCHAR(4000) AS
    BEGIN
    DECLARE @i		INT
    ,  @cWork		NVARCHAR(4000)
    
    SET @cWork = Upper(Left(@pcIn, 1)) + Lower(SubString(@pcIn, 2, 4000))
    
    SET @i = PatIndex('%[^0-9A-Za-z][a-z]%'
    ,  @cWork COLLATE Latin1_General_BIN)
    
    WHILE 0 < @i
       BEGIN
          SET @cWork = Left(@cWork, @i) 
    +        Upper(SubString(@cWork, 1 + @i, 1)) + SubString(@cWork, 2 + @i, 4000)
          SET @i = PatIndex('%[^0-9A-Za-z][a-z]%'
    ,        @cWork COLLATE Latin1_General_BIN)
       END
    
    RETURN @cWork
    END
    GO
    -PatP

  5. #5
    Join Date
    Feb 2004
    Posts
    42
    Worked great! Thanks!!!

Posting Permissions

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