Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 573
  • Last Modified:

Proper Case (sentence format)

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
0
ronyak
Asked:
ronyak
  • 3
  • 2
  • 2
  • +1
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
0
 
ronyakAuthor 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.
0
 
NightmanCTOCommented:
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.
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
ronyakAuthor 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..
0
 
NightmanCTOCommented:
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).
0
 
appariCommented:
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')
0
 
ronyakAuthor Commented:
I tried running the function but i keep getting timeout expired..

ideas?
0
 
appariCommented:
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
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now