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, November 15, 2010 6:22 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 7:28 AM
Points: 5,584, Visits: 6,380
Jan Van der Eecken (10/18/2010)
Hi Brandie,

I guess the final update back will fail on a database with a case sensitive collation. Shouldn't it rather read:

Update idf
Set Location = mt1.Location
from dbo.Import_Data_Filter idf
join dbo.#MyTemp1 mt1
on UPPER(idf.Location) = UPPER(mt1.Location);



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.


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1020699
Posted Monday, November 15, 2010 6:24 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 7:28 AM
Points: 5,584, Visits: 6,380
Carl Federl (10/18/2010)
Perhaps I am missing something but what is wrong with a solution that has no cursors, whiles or UDFs ?


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.


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1020703
Posted Monday, November 15, 2010 6:29 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 7:28 AM
Points: 5,584, Visits: 6,380
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.


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1020708
Posted Monday, November 15, 2010 6:30 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, October 19, 2014 2:24 AM
Points: 15, Visits: 77
Why nobody likes my solution without UDF, WHILEs, Dynamic SQL statments, etc ...
:)

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 ALL
Select 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)<>0
if @@rowcount>0 goto r

select ltrim(location) from #Import_Data_Filter

drop table #Import_Data_Filter

Post #1020710
Posted Monday, November 15, 2010 6:57 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:54 AM
Points: 6,813, Visits: 14,028
arty 15255 (11/15/2010)
Why nobody likes my solution without UDF, WHILEs, Dynamic SQL statments, etc ...
:)
...snip...


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?


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1020742
Posted Monday, November 15, 2010 7:02 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, October 19, 2014 2:24 AM
Points: 15, Visits: 77
Does that mean that looping through each character is better?
Post #1020745
Posted Monday, November 15, 2010 7:04 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 26, 2014 3:23 PM
Points: 2,116, Visits: 6,439
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).


--------------------------------------------------------------------------
A little knowledge is a dangerous thing (Alexander Pope)

In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)
Post #1020752
Posted Monday, November 15, 2010 7:09 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:54 AM
Points: 6,813, Visits: 14,028
arty 15255 (11/15/2010)
Does that mean that looping through each character is better?


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"



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1020757
Posted Monday, November 15, 2010 7:10 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, October 19, 2014 2:24 AM
Points: 15, Visits: 77
Now it is clear.
Thank you,
Post #1020761
Posted Monday, November 15, 2010 7:14 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, October 19, 2014 2:24 AM
Points: 15, Visits: 77
Then, here is mine version of UDF:

create function fn_convert_ucase
(@s nvarchar(max))
returns nvarchar(max)
as
begin
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 @s
end

Post #1020764
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse