How to transform a string to mixed case
-
Hi there,
Is there any trick in T-SQL to transform a string with a person's name into a string with the initial letter of each name uppercase and remaining lowercase ?
In the string functions I didn't find anything to do this (as I expected). Unfortunately I also didn't find a function to break a fullname into its individual names, so that in a cycle I could change the first letter.
Does anyone know a link or a nice algorithm to transform with T-SQL
'JOHN DOE' or 'john doe' into 'John Doe' ?
Thanks
Miguel
-
Try searching for "InitCap"
Here's a simple C# implementation
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 InitCap(SqlString txt)
{
return txt.IsNull ? SqlString.Null : new CultureInfo(txt.CultureInfo.Name).TextInfo.ToTitleCase(txt.Value.ToLower());
}
};
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Having worked for a mass-mailer for a few years, I would recommend you do not do this. People are touchy about their names and what you are asking cannot be done with 100% accuracy.
Some names can be capitalized more than one way O'Connell and O'connell are, in fact, both correct depending on the person. When you get to dutch names, you will find capitalization in the middle of a name. You may also find that you have some names that are really abbreviations if you have an extensive enough set of data (CC should not be turned into Cc).
You need to at least manually check everything and the ones with ambiguous rules should just be left alone.
Like I said, people are weird about this and a small group of upset customers can become a large problem very quickly.
-
If you want to do this, I suggest using a combination of Substring() and PatIndex(). Use the PatIndex() to find the spaces in your names and then the Substring() to LOWER() the strings after the 1st character to the space and the characters after the space + 1 character.
Check out CharIndex() also, in case that might help you.
-
Hi,
Look at this. This might help you.
DECLARE @Name VARCHAR(30)
DECLARE @Length INT
SET @Name = 'sample example'
SET @Length = LEN(@Name)
DECLARE @p VARCHAR(30)
SET @p = PATINDEX('% %',@Name)
DECLARE @q VARCHAR(30)
SET @q = LEN(SUBSTRING(@Name,@p+1,@Length))
IF (@p = 0)
BEGIN
DECLARE @Nameame VARCHAR(30)
SET @Nameame = SUBSTRING(@Name,2,@Length)
SELECT UPPER(SUBSTRING(@Name,1,1)) + @Nameame
END
ELSE
BEGIN
DECLARE @Nameame2 VARCHAR(30)
SET @Nameame2 = SUBSTRING(@Name,2,@Length)
SET @Nameame2 = LOWER(@Nameame2)
DECLARE @s-2 VARCHAR(30)
SET @s-2 = UPPER(SUBSTRING(@Name,1,1)) + @Nameame2
--select stuff(@s,@p+1,@Length,' ')
DECLARE @w VARCHAR(30)
SET @w = RIGHT(@Name,@q)
DECLARE @Length1 INT
SET @Length1 = LEN(@w)
DECLARE @Nameame1 VARCHAR(30)
SET @Nameame1 = SUBSTRING(@w,2,@Length1-1)
SET @Nameame1 = LOWER(@Nameame1)
SELECT STUFF(@s,@p+1,@Length,'') + UPPER(SUBSTRING(@w,1,1)) + @Nameame1
END
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply