Labels

Apache Hadoop (3) ASP.NET (2) AWS S3 (2) Batch Script (3) BigQuery (21) BlobStorage (1) C# (3) Cloudera (1) Command (2) Data Model (3) Data Science (1) Django (1) Docker (1) ETL (7) Google Cloud (5) GPG (2) Hadoop (2) Hive (3) Luigi (1) MDX (21) Mongo (3) MYSQL (3) Pandas (1) Pentaho Data Integration (5) PentahoAdmin (13) Polybase (1) Postgres (1) PPS 2007 (2) Python (13) R Program (1) Redshift (3) SQL 2016 (2) SQL Error Fix (18) SQL Performance (1) SQL2012 (7) SQOOP (1) SSAS (20) SSH (1) SSIS (42) SSRS (17) T-SQL (75) Talend (3) Vagrant (1) Virtual Machine (2) WinSCP (1)

Wednesday, February 16, 2011

SQL Function to Display Text with Sentence Caps

Below Query creates a function to display the text in sentence caps, e.g.., displays 'ANANDH KUMAR' as 'Anandh Kumar'

CREATE FUNCTION [dbo].[ProperCase](@Input AS VARCHAR(8000))
RETURNS VARCHAR(8000)
AS
BEGIN
   DECLARE @Reset BIT;
   DECLARE @Ret VARCHAR(8000);
   DECLARE @i INT;
   DECLARE @c CHAR(1);

   SELECT @Reset = 1, @i=1, @Ret = '';
  
   WHILE (@i <= LEN(@Input))
      SELECT @c= SUBSTRING(@Input,@i,1),
               @Ret = @Ret + CASE WHEN @Reset=1 THEN UPPER(@c) ELSE LOWER(@c) END,
               @Reset = CASE WHEN @c LIKE '[a-zA-Z]' THEN 0 ELSE 1 END,
               @i = @i +1
              
   RETURN @Ret
END

No comments:

Post a Comment