Lee posted on August 5, 2011 11:03

Someone just asked me if there's a built-in function in TSQL that takes a string and changes the first letter of each word to upper case. I told them hell I don't know but I think not;  here's a way to do it without a bunch of char functions...use a CLR UDF. One line and all works pretty well:

 

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Globalization;

public partial class UserDefinedFunctions

{
  [Microsoft.SqlServer.Server.SqlFunction]

  public static SqlString UpperFirst(string value)
  {

   return new SqlString(CultureInfo.CurrentCulture.TextInfo.ToTitleCase(value));

  }
};

 

sp_configure 'clr enable',1
GO
RECONFIGURE
GO
SELECT dbo.UpperFirst('TSQL upper case first letter of each word')
GO

 

 


Posted in: .Net , Beginner , TSQL  Tags:
blog comments powered by Disqus

by Lee Everest, M.S.

Info

Poll

Do you use Azure or cloud in your organization?



Show Results

Ads

Search


Month List

Calendar

«  May 2012  »
MoTuWeThFrSaSu
30123456
78910111213
14151617181920
21222324252627
28293031123
45678910
View posts in large calendar

Tags

Disclaimer
The opinions, code, examples, et.al. expressed herein are my own personal opinions and do not represent my employer's view in any way, shape form, or fashion.  All code for demonstration purposes - no guarantees, either written or implied, are made.

© Copyright 2012 Lee Everest's SQL Server, etc. weblog