SQLServerCentral / Discuss Content Posted by Brandie Tarvin / Article Discussions / Article Discussions by Author / Detective Stories - Changing the Case / Latest Posts

RE: Detective Stories - Changing the Case

Brandie Tarvin — Wed, 13 Apr 2011 11:42:09 GMT

That's an interesting piece of code. Thanks for posting it.

RE: Detective Stories - Changing the Case

Caruncles — Wed, 13 Apr 2011 11:08:40 GMT

We use the function below, i.e. fProper(fieldname). Works great.USE [SfiData]GO/****** Object: UserDefinedFunction [dbo].[fProper] Script Date: 04/13/2011 13:05:27 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER FUNCTION [dbo].[fProper] (@tcString VARCHAR(100)) RETURNS VARCHAR(100) ASBEGIN -- Scratch variables used for processing DECLARE @workString VARCHAR(100) DECLARE @outputString VARCHAR(100) DECLARE @stringLength INT DECLARE @loopCounter INT DECLARE @charAtPos VARCHAR(1) DECLARE @wordStart INT -- If the incoming string is NULL, return an error IF (@tcString IS NULL) RETURN ('(no string passed)') -- Initialize the scratch variables SET @workString = LOWER(@tcString) SET @outputString = '' SET @stringLength = LEN (@tcString) SET @loopCounter = 1 SET @wordStart = 1 -- Loop over the string WHILE (@loopCounter <= @stringLength) BEGIN -- Get the single character off the string SET @charAtPos = SUBSTRING (@workString, @loopCounter, 1) -- If we are the start of a word, uppercase the character -- and reset the work indicator IF (@wordStart = 1) BEGIN SET @charAtPos = UPPER (@charAtPos) SET @wordStart = 0 END -- If we encounter a white space, indicate that we -- are about to start a word IF (@charAtPos = ' ') SET @wordStart = 1 -- Form the output string SET @outputString = @outputString + @charAtPos SET @loopCounter = @loopCounter + 1 END -- Return the final output RETURN (@outputString)END

RE: Detective Stories - Changing the Case

arty 15255 — Mon, 15 Nov 2010 07:14:53 GMT

Then, here is mine version of UDF:[code="sql"]create function fn_convert_ucase(@s nvarchar(max))returns nvarchar(max)asbegin if @s is null goto ex set @s = char(160)+replace(@s,' ',char(160)) while charindex(char(160),@s)<>0 begin set @s = replace(@s,char(160)+substring(@s,charindex(char(160),@s)+1,1),' '+UPPER(substring(@s,charindex(char(160),@s)+1,1))) end ex: return @send[/code]

RE: Detective Stories - Changing the Case

arty 15255 — Mon, 15 Nov 2010 07:10:45 GMT

Now it is clear.Thank you,

RE: Detective Stories - Changing the Case

ChrisM@Work — Mon, 15 Nov 2010 07:09:03 GMT

[quote][b]arty 15255 (11/15/2010)[/b][hr]Does that mean that looping through each character is better?[/quote]Yes, if it means avoiding an unnecessary table scan!if @@rowcount>0 goto r"If the last iteration f the UPDATE performed any work, then scan the table again"

RE: Detective Stories - Changing the Case

Jan Van der Eecken — Mon, 15 Nov 2010 07:04:23 GMT

Hi Brandie,No it would not, it is just in the join that it compares the upper-case version of the camel-cased string to the upper-case version of the original string. If you don't do that on a case-sensitive database, then that join would not return any matches, and no updates would take place at all (assuming that the original and the camel-cased versions to indeed differ).

RE: Detective Stories - Changing the Case

arty 15255 — Mon, 15 Nov 2010 07:02:05 GMT

Does that mean that looping through each character is better?

RE: Detective Stories - Changing the Case

ChrisM@Work — Mon, 15 Nov 2010 06:57:01 GMT

[quote][b]arty 15255 (11/15/2010)[/b][hr]Why nobody likes my solution without UDF, WHILEs, Dynamic SQL statments, etc ... :)...snip...[/quote]Because it has a loop? Or because it has to perform n table scans, where n = the maximum number of words in the target column?

RE: Detective Stories - Changing the Case

arty 15255 — Mon, 15 Nov 2010 06:30:20 GMT

Why nobody likes my solution without UDF, WHILEs, Dynamic SQL statments, etc ... :)[code="sql"]Create Table #Import_Data_Filter (MyID Int Identity(1,1) NOT NULL, Location varchar(100))Insert into #Import_Data_Filter (Location)(Select Lower('Mandarin') UNION ALL Select Lower('San Jose') UNION ALL Select Lower('Baymeadows') UNION ALLSelect Lower('My FH Locale') UNION ALL Select Lower('St. Augustine') UNION ALL Select Lower('Test For Three Spaces')UNION ALL Select Lower('Test for being Four Spaces') UNION ALL Select Lower('Test for being Five More Spaces') UNION ALL Select Lower('Baymeadows') UNION ALL Select Lower('St. Augustine'))update #Import_Data_Filter set location = char(160)+replace(location,' ',char(160)) r: update #import_data_filter set location = replace( location, char(160)+substring(location,charindex(char(160),location)+1,1), ' '+UPPER(substring(location,charindex(char(160),location)+1,1)) ) where charindex(char(160),location)<>0if @@rowcount>0 goto rselect ltrim(location) from #Import_Data_Filterdrop table #Import_Data_Filter[/code]

RE: Detective Stories - Changing the Case

Brandie Tarvin — Mon, 15 Nov 2010 06:29:25 GMT

Thanks to everyone who's posted a response. I see I have a lot of testing to do.Someone mentioned removing extraneous spaces. In this particular case, there are no "extraneous" spaces. The spaces are supposed to be there. We wouldn't want "Little Rock", as in the city from Arkansas, to end up as "Littlerock" or "LittleRock" as that would be bad data. And if there were extraneous spaces, I wouldn't have had to jump through hoops to get them. Just do a REPLACE(location,Space(1),'') and then capitalize the first letter without a looping process.Still, I appreciate all the input. I do agree that if there are this many solutions to the problem that SQL Server aught to have a proper function for this.

RE: Detective Stories - Changing the Case

Brandie Tarvin — Mon, 15 Nov 2010 06:24:39 GMT

[quote][b]Carl Federl (10/18/2010)[/b][hr]Perhaps I am missing something but what is wrong with a solution that has no cursors, whiles or UDFs ?[/quote]Maybe I'm misreading your solution, but it doesn't seem to account for spaces between names. Such as "St. Augustine". Your solution would make it "St. augustine".I haven't tested this, though. I'm going off what I see.

RE: Detective Stories - Changing the Case

Brandie Tarvin — Mon, 15 Nov 2010 06:22:21 GMT

[quote][b]Jan Van der Eecken (10/18/2010)[/b][hr]Hi Brandie,I guess the final update back will fail on a database with a case sensitive collation. Shouldn't it rather read:[code="sql"]Update idfSet Location = mt1.Locationfrom dbo.Import_Data_Filter idfjoin dbo.#MyTemp1 mt1on UPPER(idf.Location) = UPPER(mt1.Location);[/code][/quote]Sorry it's been so long since you posted. I was working on massive projects.Actually, Jan, your code would set everything back to Upper case which is what the customer did NOT want. He wanted Camel Case names.

RE: Detective Stories - Changing the Case

wiki 82367 — Tue, 19 Oct 2010 11:53:01 GMT

Similar to some of the posts above - here's my version using a function. It performs well.Here's the function:[code="sql"]CREATE FUNCTION dbo.fn_title_case( @string varchar(max))RETURNS varchar(max)ASBEGINDECLARE @curr char(1);DECLARE @len int;DECLARE @loc int;DECLARE @out_string varchar(max);DECLARE @prev_alpha char(1);SET @out_string = '';SET @prev_alpha = 'N'SET @len = LEN(@string);SET @loc = 1;WHILE @loc <= @lenBEGIN SET @curr = SUBSTRING(@string,@loc,1); IF @curr LIKE '[A-Z0-9]' BEGIN IF @prev_alpha = 'Y' SET @out_string = @out_string + LOWER(@curr); ELSE SET @out_string = @out_string + UPPER(@curr); SET @prev_alpha = 'Y'; END ELSE BEGIN SET @out_string = @out_string + @curr; SET @prev_alpha = 'N'; END SET @loc = @loc + 1;END RETURN @out_string;END[/code]Then apply it as follows (using the table name from the original post):[code="sql"]SELECT dbo.fn_decapitalise(Location) into #result from Import_Data_Filter;[/code]

RE: Detective Stories - Changing the Case

Gene Porter — Tue, 19 Oct 2010 09:22:13 GMT

Here's a UDF that also removes extraneous spaces.ALTER FUNCTION [dbo].[udfCamelCase]( -- Add the parameters for the function here @VString varchar(1000))RETURNS varchar(1000)ASBEGIN -- Table used to get the words in the string DECLARE @PartTable table (StringPart varchar(100)); -- Variable to hold each part of the return string DECLARE @HWord varchar(100); -- These variables are used to "clean" the string, i.e., make sure only -- one space exists between the parts of the string DECLARE @CN varchar(200); DECLARE @X varchar(100); DECLARE @I int; DECLARE @HString varchar(1000); DECLARE @RetVar varchar(1000); IF @VString = '' OR @VString IS NULL RETURN ''; SET @HString = RTRIM(LTRIM(@VString)); SET @I = CHARINDEX(' ',@HString); -- If there are no spaces just make the first character upper case and return it IF @I = 0 BEGIN SET @RetVar = UPPER(SUBSTRING(@HString,1,1)); SET @RetVar = @RetVar + LOWER(SUBSTRING(@HString,2,LEN(@HString) - 1)); RETURN @RetVar; END WHILE (@I != 0) BEGIN SET @RetVar = UPPER(SUBSTRING(@HString,1,1)); SET @RetVar = @RetVar + LOWER(SUBSTRING(@HString,2,@I - 1)); INSERT @PartTable SELECT @RetVar; SET @HString = LTRIM(RTRIM(SUBSTRING(@HString,@I+1,LEN(@HString) - @I))); SET @I = CHARINDEX(' ',@HString); END IF LEN(@HString) > 0 BEGIN SET @RetVar = UPPER(SUBSTRING(@HString,1,1)); SET @RetVar = @RetVar + LOWER(SUBSTRING(@HString,2,LEN(@HString) - 1)); INSERT @PartTable SELECT @RetVar; END SET @HString = ''; WHILE ((SELECT COUNT(*) FROM @PartTable) > 0) BEGIN SELECT TOP (1) @HWord = LTRIM(RTRIM(StringPart)) FROM @PartTable; DELETE TOP (1) FROM @PartTable; IF @HString = '' SET @HString = @HWord; ELSE SET @HString = @HString + ' ' + @HWord; END --RETURN @DBVar; RETURN @HString;END

RE: Detective Stories - Changing the Case

Mark Tierney — Tue, 19 Oct 2010 09:11:34 GMT

I also too the function route[code="other"]create function uf_mixed (@p_col varchar(max))returns varchar(max) asbegindeclare @lgth int, @ctr int, @found varchar(1), @v_col varchar(max), @nxt intselect @lgth = LEN(@p_col), @ctr = 1, @found = 'x', @nxt = 0if @lgth > 0beginselect @v_col = upper(SUBSTRING(@p_col,1,1))select @ctr = @ctr + 1while (@ctr <= @lgth)begin select @found = SUBSTRING(@p_col,@ctr,1) if @found = ' ' select @nxt = @ctr+1 if @nxt = @ctr select @v_col = @v_col + upper(SUBSTRING(@p_col,@ctr,1)) else select @v_col = @v_col + lower(SUBSTRING(@p_col,@ctr,1)) select @ctr = @ctr + 1 endendreturn(@v_col)endgoupdate Import_Data_Filter set Location = dbo.uf_mixed(Location)[/code]

RE: Detective Stories - Changing the Case

Venkataraman R — Tue, 19 Oct 2010 06:15:46 GMT

Same Userdefined Function, tried with PATINDEX. [code="sql"]CREATE FUNCTION [CapitalCase] ( @Input varchar(255) ) RETURNS varchar(255) AS BEGIN DECLARE @Results varchar(255) Declare @NextWord int, @NextSpace int if len(@Input)>0 Begin -- Trimming the input Set @Input = lower(ltrim(rtrim(@Input))) -- Finding next word in the input Set @NextWord = patindex('%[a-zA-Z]%',@Input) -- Initializing the result Set @Results = '' -- If there is a word beginning, then make it Capital case While @NextWord <> 0 Begin -- Capital casing first character of first word and adding to result Set @Results = @Results + UPPER(substring(@Input,@NextWord,1)) -- finding next word from next space Set @NextSpace = charindex(char(32),@Input,1) -- If there is no space then words are over, write till the end of string to result and break out of the loop If @NextSpace = 0 BEGIN SET @Results = @Results +SUBSTRING(@Input,@NextWord+1,LEN(@Input)) BREAK END -- If space is present then there might be one more word existing ELSE BEGIN -- Set result set from the capital case character till the next space SET @Results = @Results + SUBSTRING(@Input,@NextWord+1,(@NextSpace - @NextWord)) -- Making input as remaining part of Input from the next space Set @Input = SUBSTRING(@Input,@NextSpace+1,LEN(@Input)) Set @NextWord = patindex('%[a-zA-Z]%',@Input) END End End Else Set @Results= '' Return @Results END [/code]

RE: Detective Stories - Changing the Case

dave.farmer — Tue, 19 Oct 2010 04:21:29 GMT

So many different solutions... suggests SQL should have a proper case function of it's own?Many moons ago I came up with this - our need is quite straightforward, lower case and capitalise each word delimited by spaces and/or hyphens. Not pretty, but it does the job it's designed to do:[code="sql"]CREATE FUNCTION [dbo].[ToProperCase](@string NVARCHAR(4000)) RETURNS NVARCHAR(4000) ASBEGIN SET @string = LOWER(LTRIM(RTRIM(@string))) DECLARE @i INT SET @i = ASCII('a') WHILE @i <= ASCII('z') BEGIN SET @string = REPLACE( @string, ' ' + CHAR(@i), ' ' + CHAR(@i-32)) SET @string = REPLACE( @string, '-' + CHAR(@i), '-' + CHAR(@i-32)) SET @i = @i + 1 END IF (ASCII(LEFT(@string, 1)) BETWEEN ASCII('a') AND ASCII ('z')) BEGIN SET @string = CHAR(ASCII(LEFT(@string, 1))-32) + RIGHT(@string, LEN(@string)-1) END RETURN @stringEND[/code]

RE: Detective Stories - Changing the Case

SQLRNNR — Mon, 18 Oct 2010 17:38:05 GMT

Thanks Brandie

RE: Detective Stories - Changing the Case

Carl Federl — Mon, 18 Oct 2010 14:44:20 GMT

If you collation is Case Insensitive, then to only update rows when values would have changed, the SQL needs to cast to a Case Sensitive collation: AND CAST( Import_Data_Fixed.LocationMixed as varchar(100) ) COLLATE SQL_Latin1_General_CP1_CS_AS <> CAST( IDF.Location as varchar(100) ) COLLATE SQL_Latin1_General_CP1_CS_AS

RE: Detective Stories - Changing the Case

Alan G-436699 — Mon, 18 Oct 2010 14:38:54 GMT

Assuming these functions have to be used for English, don't forget all the special cases like "McDonald" and "MacDonald". But if you fix these, then you don't want this to happen: "BraMcOte" or "MaHon". Addresses are an interesting one too: You probably want "Apt 1a" rather than "Apt 1A". I think a function that uses UPPER and LOWER that can be simply called in any SQL update is the way to go. Perhaps you could use a parameter to tell the function whether you are proper casing a name, address or something else so you can code in the special rules discussed above?

RE: Detective Stories - Changing the Case

Carl Federl — Mon, 18 Oct 2010 14:00:27 GMT

Perhaps I am missing something but what is wrong with a solution that has no cursors, whiles or UDFs ?[code="sql"]use tempdbgoCreate Table Import_Data_Filter (MyID Int Identity(1,1) NOT NULL, Location varchar(100))GOInsert into Import_Data_Filter (Location)(Select Upper('Mandarin') UNION ALL Select Upper('San Jose') UNION ALL Select Upper('Baymeadows') UNION ALLSelect Upper('My FH Locale') UNION ALL Select Upper('St. Augustine') UNION ALL Select Upper('Test For Three Spaces')UNION ALL Select Upper('Test for being Four Spaces') UNION ALL Select Upper('Test for being Five More Spaces') UNION ALL Select Upper('Baymeadows') UNION ALL Select Upper('St. Augustine'))GO[/code]The simplist SQL:[code="sql"]use tempdbgoBEGIN TRAN;UPDATE IDFSET Location = Import_Data_Fixed.LocationMixedFROM Import_Data_Filter as IDFJOIN (select MyID , UPPER(Substring(Location,1,1)) -- First Character is always UPPER CASE + REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( LOWER(SUBSTRING(Location,2,100)) , ' a', ' A'), ' b', ' B'), ' c', ' C'), ' d', ' D'), ' e', ' E'), ' f', ' F') , ' g', ' G'), ' h', ' H'), ' i', ' I'), ' j', ' J'), ' k', ' K'), ' l', ' L') , ' m', ' M'), ' n', ' N'), ' o', ' O'), ' p', ' P'), ' q', ' Q'), ' r', ' R') , ' s', ' S'), ' t', ' T'), ' u', ' U'), ' v', ' V'), ' w', ' W'), ' x', ' X') , ' y', ' Y'), ' z', ' Z') as LocationMixed from Import_Data_Filter ) AS Import_Data_Fixed on Import_Data_Fixed.MyID = IDF.MyID AND Import_Data_Fixed.LocationMixed <> IDF.Location; select * from Import_Data_Filter;rollback;[/code]

RE: Detective Stories - Changing the Case

mark-1062149 — Mon, 18 Oct 2010 13:47:44 GMT

I came up with this: It's a set based approach, rather than working all the way through the string. Check for any strings which are suspect then fix the first instance within the string. I also used a DONE flag so as not to hit every row in the table at every iteration.Then loop round again for any rows which still have an instance of the suspect string. I tried it with some bodged data from AdventureWorksDW..DimCustomer (18,000+ rows) and it only took 4 seconds.[code="sql"]-- CREATE TEST DATAdrop table ImportDataFilter go Create Table ImportDataFilter (MyID Int Identity(1,1) NOT NULL, Location varchar(100), Comments varchar(20))GOInsert into ImportDataFilter (Location, Comments) (Select Upper('Mandarin'),'' UNION ALL Select Upper('San Jose'),'' UNION ALL Select Upper('Baymeadows'),'' UNION ALL Select Upper('My FH Locale'),'' UNION ALL Select Upper('St. Augustine'),'' UNION ALL Select Upper('Test For Three Spaces'),'Concatenated Spaces' UNION ALL Select Upper('Test for being Four Spaces'),'Concatenated Spaces' UNION ALL Select Upper('Test for being Five More Spaces '),'Concatenated Spaces' UNION ALL Select Upper('Baymeadows'),'' UNION ALL Select Upper('St. Augustine'),'' ) -- test with leading space UNION ALL Select Upper(' Oswestry'),'' GOIf (Select Object_ID('tempdb..#MyTemp2')) is NOT NULL Drop table #MyTemp2; -- Drop temp table if it already exists Select Distinct Location into dbo.#MyTemp2 from dbo.ImportDataFilter; --Get distinct values for all locations Update mt1 Set Location = Lower(mt1.Location) from dbo.#MyTemp2 mt1; -- Set entire string to lower case letters-- Brandie's article mentioned double spaces - this will do the trick-- Now get rid of conatenated spaces-- simply loop while there are still instances of SPACESPACEDeclare @spFlag int , @debugcount intselect @spFlag = 1, @debugcount = 0while @spFlag <> 0BEGIN Update #MyTemp2 set Location = replace(Location, ' ',' ') -- we only want to do the rows affected and not the whole table every time where charindex(' ', Location) > 0 -- how many rows are left : if zero then quit Select @spFlag = count(*) from #MyTemp2 where charindex(' ', Location) > 0 -- just in case we spiral out of control Select @debugcount = @debugcount +1 if @debugcount = 100 SELECT @spFlag = 0 -- just in case -- print @debugcount -- debugEND-- select * from #MyTemp2-- ===================================================================-- Now for the title case-- First: do the Very First char: a blanket approach for easeUpdate #MyTemp2 set Location = upper(left(Location,1)) + substring(Location,2,300)-- add a column to record the position of the next space which needs attentionAlter table #MyTemp2 add spacepos int, DONE intgo-- initialise update #MyTemp2 set spacepos = charindex(' ',Location), DONE = 0-- NOW LOOPDeclare @ucFlag int select @ucFlag = 1WHILE @ucFlag <> 0BEGIN-- process the Location UPDATE #MyTemp2 SET Location = LEFT(Location, spacepos) + upper(substring(Location,spacepos + 1,1)) + substring(Location,spacepos + 2,250) WHERE spacepos <> 0 and DONE = 0-- get new spacepos UPDATE #MyTemp2 SET spacepos = charindex(' ',Location, spacepos + 1) WHERE spacepos <> 0 and DONE = 0 SELECT @ucFlag = count(*) from #MyTemp2 where spacepos <> 0 -- are any spacepos now = 0 ? -- if so then set DONE UPDATE #MyTemp2 SET DONE = 1 where spacepos = 0 and DONE = 0END select * from #MyTemp2 -- then you can update your original table [/code]

RE: Detective Stories - Changing the Case

sdsiedentop — Mon, 18 Oct 2010 08:06:07 GMT

Back in the "olden days" when I did FoxPro development, I used a function called PROPER() quite frequently. I'm not sure why the SQL development team didn't include such a function in their product, but I digress... I've come up with a number of ways over the years to replicate the FoxPro PROPER() function in SQL and it makes me happy to see that I'm not the only one that needed such functionality.SQL is great and I still love FoxPro. In fact, I can do anything in FoxPro....except get a job.

RE: Detective Stories - Changing the Case

mrpolecat — Mon, 18 Oct 2010 07:24:10 GMT

I think I originally got this from Steve Jones or at least one of his posts. Made a couple of tweaks to handle Roman Numerals because we mostly use it for names.[code="sql"]SET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GOALTER procedure sp_ProperCase@table varchar(50),@field varchar(50)as-- Replace spaces with the "special" characterexec('update ' + @table + ' set ' + @field + ' = lower(replace( ' + @field + ' , '' '', ''@''))')-- Handle case 1 - First itemexec('update ' + @table + ' set ' + @field + ' = upper( substring( ltrim( ' + @field + ' ), 1, 1)) + substring( ltrim( ' + @field + ' ), 2, 80)')-- loop while there are rows with the flagexec('while exists( select * from ' + @table + ' where ' + @field + ' like ''%@%'' ) begin -- Proper case the word after the flag. update ' + @table + ' set ' + @field + ' = substring( ' + @field + ' , 1, charindex( ''@'', ' + @field + ' )) + upper( substring( ' + @field + ' , charindex( ''@'', ' + @field + ' )+1, 1 )) + substring( ' + @field + ' , charindex( ''@'', ' + @field + ' )+2, 80) where ' + @field + ' like ''%@%'' -- Remove the first flag encountered in each row update ' + @table + ' set ' + @field + ' = substring( ' + @field + ' , 1, charindex( ''@'', ' + @field + ' )-1) + '' '' + substring( ' + @field + ' , charindex( ''@'', ' + @field + ' ) + 1, 80) where ' + @field + ' like ''%@%'' end')exec('update ' + @table + ' set ' + @field + ' = replace(' + @field + ','' ii'','' II'') where ' + @field + ' like ''% ii''')exec('update ' + @table + ' set ' + @field + ' = replace(' + @field + ','' iii'','' III'') where ' + @field + ' like ''% iii''')GOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GO[/code]

RE: Detective Stories - Changing the Case

ronmoses — Mon, 18 Oct 2010 06:19:44 GMT

I'll throw in my proper case function just for laughs...[code]ALTER FUNCTION [cf_ProperCase] (@String varchar(MAX))RETURNS varchar(MAX)AS BEGIN DECLARE @ReturnString varchar(max), @i int; SET @ReturnString = '' SET @i = 1 WHILE @i <= LEN(@String) BEGIN SET @ReturnString = @ReturnString + (CASE WHEN (@i = 1 OR SUBSTRING(@String, @i - 1, 1) = ' ') THEN UPPER(SUBSTRING(@String, @i, 1)) ELSE LOWER(SUBSTRING(@String, @i, 1)) END) SET @i = @i + 1 END RETURN @ReturnStringEND[/code]Very brute-force, but it works.Ron Moses

RE: Detective Stories - Changing the Case

Jan Van der Eecken — Mon, 18 Oct 2010 06:13:48 GMT

Hi Brandie,I guess the final update back will fail on a database with a case sensitive collation. Shouldn't it rather read:[code="sql"]Update idfSet Location = mt1.Locationfrom dbo.Import_Data_Filter idfjoin dbo.#MyTemp1 mt1on UPPER(idf.Location) = UPPER(mt1.Location);[/code]

RE: Detective Stories - Changing the Case

sjones — Mon, 18 Oct 2010 03:12:26 GMT

I inherited this function which uses regular expressions to look for where a letter should be capitalised. In the current incarnation, it looks for the pattern, space, comma, apostrophe (uses '', because the apostrophe will be embedded in a string, followed by a letter.[code="sql"]ALTER FUNCTION [dbo].[fnProperCase] ( @String varchar(1023)) RETURNS varchar(1023) AS BEGIN DECLARE @Pos int, @Temp varchar(1023), @Result varchar(1023) SET @Temp = LOWER(LTRIM(RTRIM(@String))) SET @Result = UPPER(SUBSTRING(@Temp,1,1)) SET @Temp = SUBSTRING(@Temp,2,1022) SET @Pos = PATINDEX('%[ ,''-][a-z]%', @Temp) WHILE @Pos > 0 BEGIN SET @Result = @Result + SUBSTRING(@Temp,1,@Pos) + UPPER(SUBSTRING(@Temp,@Pos+1,1)) SET @Temp = SUBSTRING(@Temp, @Pos + 2, 1022) SET @Pos = PATINDEX('%[ ,''-][a-z]%', @Temp) END SET @Result = @Result + @Temp RETURN @Result END[/code]

RE: Detective Stories - Changing the Case

arty 15255 — Mon, 18 Oct 2010 01:46:43 GMT

my solution:[code="sql"]Create Table #Import_Data_Filter (MyID Int Identity(1,1) NOT NULL, Location varchar(100))Insert into #Import_Data_Filter (Location)(Select Lower('Mandarin') UNION ALL Select Lower('San Jose') UNION ALL Select Lower('Baymeadows') UNION ALLSelect Lower('My FH Locale') UNION ALL Select Lower('St. Augustine') UNION ALL Select Lower('Test For Three Spaces')UNION ALL Select Lower('Test for being Four Spaces') UNION ALL Select Lower('Test for being Five More Spaces') UNION ALL Select Lower('Baymeadows') UNION ALL Select Lower('St. Augustine'))update #Import_Data_Filter set location = char(160)+replace(location,' ',char(160)) r: update #import_data_filter set location = replace( location, char(160)+substring(location,charindex(char(160),location)+1,1), ' '+UPPER(substring(location,charindex(char(160),location)+1,1)) ) where charindex(char(160),location)<>0if @@rowcount>0 goto rselect ltrim(location) from #Import_Data_Filterdrop table #Import_Data_Filter[/code]p.s.: You can change char(160) to anything.

RE: Detective Stories - Changing the Case

robr-793239 — Sun, 17 Oct 2010 22:58:31 GMT

I've read through the examples and find the variety of techniques interesting. When I had to solve the problem I was looking at fixing the data as it went into the database, so my solution is a generalized function that can be applied with an Insert or Update process. I offer it as another solution if anyone finds it of interest.[code="sql"]CREATE FUNCTION [dbo].[FormalCase]( @Input varchar(255))RETURNS varchar(255)ASBEGIN DECLARE @Results varchar(255) if len(@Input)>0 Begin Set @Input = lower(ltrim(rtrim(@Input))) Declare @NextSpace int, @LastSpace int Set @LastSpace = 0 Set @NextSpace = charindex(char(32),@Input,1) While @NextSpace>@LastSpace Begin Set @Input = Left(@Input, @NextSpace) + upper(substring(@Input,@NextSpace + 1, 1)) + Right(@Input, len(@Input)-(@Nextspace+1)) Set @LastSpace = @NextSpace Set @NextSpace = charindex(char(32),@Input,@LastSpace + 1) End SELECT @Results = Upper(left(@Input,1)) + right(@Input, len(@Input)-1) End Else Set @Results= '' RETURN @ResultsEND/* UNIT TESTINGSelect dbo.formalcase('a stitch in time')*/GO[/code]

RE: Detective Stories - Changing the Case

LutzM — Sat, 16 Oct 2010 17:30:35 GMT

How about this?[code="sql"];WITH cte AS -- split each string by character and decide UPPER or lower based on previous character or being the first char( SELECT N, Id, CASE WHEN N=1 OR (N>1 AND SUBSTRING(location ,N-1,1 ) = ' ') THEN UPPER(SUBSTRING(location ,N ,1)) ELSE LOWER(SUBSTRING(location ,N ,1)) END AS split FROM Tally CROSS APPLY -- apply the code to each location ( SELECT Location, min(MyId) AS Id FROM Import_Data_Filter GROUP BY Location )y WHERE N < LEN(' ' + location + ' '))-- and put it back togetherSELECT REPLACE((SELECT '' + split FROM cte c2 WHERE c2.Id = c1.Id ORDER BY N FOR XML PATH('')),' ',' ')FROM cte c1GROUP BY Id[/code]

RE: Detective Stories - Changing the Case

mister.magoo — Sat, 16 Oct 2010 17:03:15 GMT

There was a thread about this a while back where Paul White showed an excellent SQLCLR method [url]http://www.sqlservercentral.com/Forums/FindPost910545.aspx[/url]

Detective Stories - Changing the Case

Brandie Tarvin — Sat, 16 Oct 2010 16:42:17 GMT

Comments posted to this topic are about the item [B]Detective Stories - Changing the Case[/B]