Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

Detective Stories - Changing the Case Expand / Collapse
Author
Message
Posted Monday, October 18, 2010 1:47 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, September 24, 2011 11:56 AM
Points: 2, Visits: 26
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.

-- CREATE TEST DATA

drop table ImportDataFilter
go
Create Table ImportDataFilter (MyID Int Identity(1,1) NOT NULL, Location varchar(100), Comments varchar(20))
GO

Insert 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'),''
GO


If (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 SPACESPACE
Declare @spFlag int , @debugcount int
select @spFlag = 1, @debugcount = 0

while @spFlag <> 0
BEGIN

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 -- debug
END

-- select * from #MyTemp2
-- ===================================================================
-- Now for the title case

-- First: do the Very First char: a blanket approach for ease

Update #MyTemp2 set Location = upper(left(Location,1)) + substring(Location,2,300)

-- add a column to record the position of the next space which needs attention

Alter table #MyTemp2 add spacepos int, DONE int
go
-- initialise
update #MyTemp2 set spacepos = charindex(' ',Location), DONE = 0

-- NOW LOOP
Declare @ucFlag int
select @ucFlag = 1

WHILE @ucFlag <> 0
BEGIN

-- 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 = 0

END

select * from #MyTemp2
-- then you can update your original table


Post #1006570
Posted Monday, October 18, 2010 2:00 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 11:23 AM
Points: 2,281, Visits: 4,233
Perhaps I am missing something but what is wrong with a solution that has no cursors, whiles or UDFs ?
use tempdb
go
Create Table Import_Data_Filter (MyID Int Identity(1,1) NOT NULL, Location varchar(100))
GO
Insert into Import_Data_Filter (Location)
(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')
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

The simplist SQL:
use tempdb
go
BEGIN TRAN;

UPDATE IDF
SET Location = Import_Data_Fixed.LocationMixed
FROM Import_Data_Filter as IDF
JOIN (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;



SQL = Scarcely Qualifies as a Language
Post #1006581
Posted Monday, October 18, 2010 2:38 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 25, 2014 2:11 PM
Points: 47, Visits: 221
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?
Post #1006599
Posted Monday, October 18, 2010 2:44 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 11:23 AM
Points: 2,281, Visits: 4,233
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


SQL = Scarcely Qualifies as a Language
Post #1006603
Posted Monday, October 18, 2010 5:38 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 11:48 AM
Points: 17,948, Visits: 15,944
Thanks Brandie



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #1006662
Posted Tuesday, October 19, 2010 4:21 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 9:54 AM
Points: 912, Visits: 654
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:
CREATE FUNCTION [dbo].[ToProperCase](@string NVARCHAR(4000)) RETURNS NVARCHAR(4000) 
AS
BEGIN

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 @string
END

Post #1006835
Posted Tuesday, October 19, 2010 6:15 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 11, 2014 6:24 AM
Points: 132, Visits: 167
Same Userdefined Function, tried with PATINDEX.

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

Post #1006896
Posted Tuesday, October 19, 2010 9:11 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, October 17, 2012 7:25 AM
Points: 30, Visits: 208
I also too the function route

create function uf_mixed (@p_col varchar(max))
returns varchar(max)
as
begin
declare @lgth int, @ctr int, @found varchar(1), @v_col varchar(max), @nxt int

select @lgth = LEN(@p_col), @ctr = 1, @found = 'x', @nxt = 0

if @lgth > 0
begin
select @v_col = upper(SUBSTRING(@p_col,1,1))
select @ctr = @ctr + 1

while (@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
end
end
return(@v_col)
end
go

update Import_Data_Filter set Location = dbo.uf_mixed(Location)




Best Regards,

Mark Tierney

Post #1007050
Posted Tuesday, October 19, 2010 9:22 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 4, 2014 2:00 PM
Points: 7, Visits: 123
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)
AS
BEGIN
-- 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
Post #1007058
Posted Tuesday, October 19, 2010 11:53 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Saturday, May 31, 2014 5:21 PM
Points: 66, Visits: 12
Similar to some of the posts above - here's my version using a function. It performs well.

Here's the function:
CREATE FUNCTION   dbo.fn_title_case
(
@string varchar(max)
)
RETURNS varchar(max)
AS
BEGIN

DECLARE @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 <= @len
BEGIN
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

Then apply it as follows (using the table name from the original post):
SELECT dbo.fn_decapitalise(Location) into #result from Import_Data_Filter;


Post #1007199
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse