Proper Case (sentence format)

ronyak
ronyak used Ask the Experts™
on
Hello, I have a table with rows of data formated in various cases. Example: "THis is A hAppy Cat. sometimes This cat is Unhappy."

I would like to use a function in a query that can turn it into "This is a happy cat. Somtimes this cat is unhappy."

I do not want to capitalize the first letter of every word, however only the first letter of the first word in each sentence for proper formating.

Thanks for your help on this.

Ron
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:

Author

Commented:
I do not want to capitalize the first letter of every word, however only the first letter of the first word in each sentence for proper formating.

Commented:
Hi ronyak,

You should be able to derive some of the required logic from that function. However, I have to ask why you would want to do this in T-SQL. T-SQL is great for set-based operations, but string manipulation is NOT it's strongest point (and is resource intensive). That is best left to client applications.

The CLR (.NET) is a good start - there are lot's of examples of tokenisers out on the net (our very own gregoryyoung has a good example of a high performance tokeniser at http://codebetter.com/blogs/gregyoung/archive/2006/08/15/148292.aspx - true, a simple word count but can eaasily be adapted for your requirements). I would suggest validating the input before allowing it into SQL Server. Once you have the base code, you can always code a seperate application to clean up your history.
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Author

Commented:
Thx, i'm not very experienced besides MS Access and basic SQL, i was hoping for a function i could put in a query like the propercase function on the link above.. I have a dts package that exports to a text file and this query cleans up the data before export..

Commented:
Look, you CAN do this in T-SQL. But just because you can doesn't mean it's a good idea (Tex Johnstone barrel-rolled a Boeing 707 in 1955 - just because you can doesn'tmean that you should!)

Performing that kind of operation in T-SQL with more than a few rows is a server killer.

Either clean up on the way in, or at the least have some kind of batch process that cleans up the data in SQL on a regular basis (off-peak, maintenance task when no-one is really hitting the server).

Commented:
try like this, In this function sentence separator is supposed as '. ' . you may be required to change the function to handle '.'

Alter Function Propercase(@srcVal varchar(2000)) returns varchar(2000)
as
begin
      declare @temp varchar(2000)
      declare @curPos int
      select @temp = lower(@srcVal), @curPos=0
      select @temp = upper(substring(@temp,1,1)) + substring(@temp,2,len(@temp)-1)
      while charindex('. ',@temp,@curPos+1)<>0
      begin
            select @curPos = charindex('. ',@temp)
            select @temp = substring(@temp,1,@curPos+1) +
                                          upper(substring(@temp,@curPos+2,1)) +
                                          substring(@temp,@curPos+3,len(@temp)-@curPos-2)
      end

      return @temp
end


select dbo.Propercase('THis is A hAppy Cat. sometimes This cat is Unhappy')

Author

Commented:
I tried running the function but i keep getting timeout expired..

ideas?
Commented:
small miss in the unction, try this

Alter Function Propercase(@srcVal varchar(2000)) returns varchar(2000)
as
begin
     declare @temp varchar(2000)
     declare @curPos int
     select @temp = lower(@srcVal), @curPos=0
     select @temp = upper(substring(@temp,1,1)) + substring(@temp,2,len(@temp)-1)
     while charindex('. ',@temp,@curPos+1)<>0
     begin
          select @curPos = charindex('. ',@temp,@curPos+1)
          select @temp = substring(@temp,1,@curPos+1) +
                                   upper(substring(@temp,@curPos+2,1)) +
                                   substring(@temp,@curPos+3,len(@temp)-@curPos-2)
     end

     return @temp
end

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial