none
convert any uppercase sentence as normal sentence RRS feed

  • Question

  • I have one column of  upper case sentance, but I want to it as normal sentance. As example

    My Sentance is like:--

    MR. VINEER AGARWAL IS WITH THE COMPANY SINCE LAST OVER 7 YEARS. HE HAS DONE GRADUATION IN ECONOMICS AND INDUSTRIAL MANAGEMENT FROM USA. AN IT SAWY PERSON, MR. AGARWAL HAS SUCCESSFULLY INTRODUCED COMPUTERIZATION TO THE BRANCH LEVEL BESIDES FACILITATING TIMELY DATA TRANSFER COVERING WIDE BRANCH NETWORK.    AS A RESULT OF HIS CONTINUED EFFORTS, THE COMPANY HAS MADE ITS PRESENCE FELT IN THE NICHE GROWTH SEGMENTS VIZ THIRD PARTY LOGISTICS AND EXPRESS CARGO SERVICES ETC.

    I have approx more than 2000 rows like this sentance,  to mannually convert it, Its very trippicle so, I want it as simple sentance. How I can do it, plz help

    My result may be like :--

    Mr. Vineer Agrarwal is with the company since last .. 

    as

     

    Tuesday, June 20, 2006 5:17 AM

Answers

  • Hello...

     

    I think you are out of luck with this problem. The main problem is the correct capitilisation of names.

    Also SQL Server is only supplying very "basic" tools to modify the case of a string (lower() and upper()). To get a slight chance to do this electronically you could take a closer look at regular expressions. Those would allow you to get the start of a new sentence correct. But what will really be nasty will be the names. Those have to be hand edited. So basically you could do a lower() and edit it afterwards, but that will still be a lot of work...

     

     

    Tuesday, June 20, 2006 5:58 AM
  • Another version of the ProperCase function - allows you to input exceptions:

    http://www.wisesoft.co.uk/Articles/SQL%20Server/ProperCaseUserDefninedFunction.aspx

    Thursday, June 29, 2006 10:06 PM

All replies

  • Hello...

     

    I think you are out of luck with this problem. The main problem is the correct capitilisation of names.

    Also SQL Server is only supplying very "basic" tools to modify the case of a string (lower() and upper()). To get a slight chance to do this electronically you could take a closer look at regular expressions. Those would allow you to get the start of a new sentence correct. But what will really be nasty will be the names. Those have to be hand edited. So basically you could do a lower() and edit it afterwards, but that will still be a lot of work...

     

     

    Tuesday, June 20, 2006 5:58 AM
  • Below is a function that will capitalize your example text like this:

    Mr. Vineer Agarwal Is With The Company Since Last Over 7 Years. He Has Done Graduation In Economics And Industrial Management From Usa. An It Sawy Person, Mr. Agarwal Has Successfully Introduced Computerization To The Branch Level Besides Facilitating Timely Data Transfer Covering Wide Branch Network. As A Result Of His Continued Efforts, The Company Has Made Its Presence Felt In The Niche Growth Segments Viz Third Party Logistics And Express Cargo Services Etc.

    It's pretty simple in that it just capitalizes the first letter in each word, and lower cases the rest. Any special 'touch ups' like McDonald etc, you have to do by hand.
    As an added bonus, it also trims multiple spaces into a single space.

     

    -- start script
    if object_id('dbo.properCase') is not null drop function dbo.properCase
    go
    create function dbo.properCase ( @string varchar(8000) )
    returns varchar(8000)
    as
    -- T-SQL combined whitespace trimmer and 'proper case' function - SQL Server 2000
    -- 2002-10-23 © Kenneth Wilhelmsson
    -- Example: Select dbo.properCase(notes) from pubs..titles to see all notes proper cased.
    -- Example2: select dbo.properCase('   a   somewhat irregular     string    ')
    begin
     -- return null if input is null
     if ( @string is null ) return null

     declare @test varchar(8000), @propcase varchar(8000)
     -- init
     select @propcase = ''
     -- remove embedded multiple spaces between words
     -- we do this by replacing 2 spaces with 1 as long as there are pairs...
     while patindex('%  %', @string) > 0
       begin
        select @string = replace(@string,'  ',' ')
       end

     -- if we still have leading spaces..
     while substring(@string,1,1) = ' '
       begin
         set @string = substring(@string,2,8000)
       end

     -- or trailing spaces...
     set @string = reverse(@string)
     while substring(@string,1,1) = ' '
       begin
         set @string = substring(@string,2,8000)
       end
     set @string = reverse(@string)

     -- done, if there still are spaces inside, begin...
      while patindex('% %',@string) > 0 
       begin
        -- grab the first 'word'
        set @test = substring(@string,1,charindex(' ',@string))  
        -- then propCase it
        set @test =  upper(substring(@test,1,1)) +
       lower(substring(@test,2,len(@test)-1)) + ' '  
        -- save away the cased word
        set @propcase = @propcase + @test
        -- trim the string from the used 'word'
        set @string = substring(@string,(len(@test)+2),8000)
        -- if there are more spaces - do it all over again with the next 'word'....
        -- except, if the 2nd next char is a space - then trim away one
         while substring(@string,1,1) = ' ' and substring(@string,2,1) = ' '
          begin
           set @string = substring(@string,2,8000)
          end
       end

     -- if no spaces, it's a single word, or the last word from the loop....
     if patindex('% %',@string) = 0 
       begin
        -- then just propCase the single word
        set @test =  upper(substring(@string,1,1)) +
       lower(substring(@string,2,len(@string)-1))
        -- save away the cased word
        set @propcase = @propcase + @test
       end
     -- done
     return @propcase
    end
    go
    -- end script

    =;o)
    /Kenneth

    Tuesday, June 20, 2006 7:08 AM
  • Thanks you for ur support, but i have proble also not cleared yet because i don't want to convert whole string first letter caps, I just want few words like(Ex:-Mr. S. Santhanakrishnan is a Chartered Accountant and has set-up Sridhar & Santhanam in the year 1977 and it is a reputed national level firm now. Today the firm has 12 Partners and over 150 professionals with presence in Chennai, Bangalore and Mumbai in India.)  In this context u can see only some words are in caps.

    So, Plz if u can further give help, its very well for me.

    Tuesday, June 20, 2006 9:07 AM
  • Another version of the ProperCase function - allows you to input exceptions:

    http://www.wisesoft.co.uk/Articles/SQL%20Server/ProperCaseUserDefninedFunction.aspx

    Thursday, June 29, 2006 10:06 PM
  • SQL 2005 offers the tool you need to change your sentence to "proper case".  If you don't have SQL 2005 you could use MS Access and apply the "proper" code to format your words.

     

    Regards,  Marie

    Monday, April 23, 2007 7:12 PM
  • Marie,

     

    I couldn't find anything in SQL Server 2005 documentation that describes this tool.

     

    Would you mind giving a reference, e.g., a link to a documentation page?

     

    Thanks in advance.

     

    Dan

    Monday, April 23, 2007 8:21 PM
  • As others have said, your 'stated desires' are virtually impossible -at least without creating a 'dictionary' table, a slew of rules, a rules engine - and in general, way too much effort.

     

    Here is a slightly more efficient version of a ProperCase function.

     

    It does, however, use a 'Numbers' table.

     

    (NOT mine, but I can't remember who to attribute the work to...)

     

    Code Snippet


    /************************************
       ProperCase Function Using Numbers Table
    ************************************/


    -- Prepare Numbers Table
    CREATE TABLE Numbers ( n int )
    DECLARE @n int
    WHILE @n < 8000
       BEGIN
          INSERT INTO Numbers VALUES ( @n )
          SET @n = ( @n + 1 )
       END
    -- End Numbers Table


    CREATE FUNCTION dbo.ProperCase
       ( @Text varchar(8000) )
       RETURNS varchar(8000)
    AS
       BEGIN
          DECLARE @Output varchar(8000)
          SELECT @Output = ''

          SELECT @Output = @Output +
             CASE
                WHEN ThisChar LIKE '[a-zA-Z]' AND LastChar NOT LIKE '[a-zA-Z]'
                   THEN upper( ThisChar )
                ELSE lower( ThisChar )
             END
          FROM (SELECT
                   substring( @Text, n, 1 )   ThisChar,
                   substring( @Text, n - 1, 1 ) LastChar
                FROM Numbers
               ) d
          RETURN @Output
       END

      
    SELECT dbo.ProperCase('this, my friends, is a test. wHat DO you think? i like shaquile o''neal')


    -----------------------------------------------------------------------
    This, My Friends, Is A Test. What Do You Think? I Like Shaquile O'Neal

     

     

    Wednesday, April 25, 2007 5:15 AM
    Moderator