Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# lower case on last name

Posted on 2013-05-22
Medium Priority
360 Views
Runing the following script to update first and lastname

update dbo.mytable\$
set       firstname = Replace(Firstname,reverse(left(reverse(firstname),len(firstname)-1)),  reverse(lower(left(reverse(firstname),len(firstname)-1)))),
lastname = Replace(Lastname,reverse(left(reverse(Lastname),len(Lastname)-1)),  reverse(lower(left(reverse(Lastname),len(Lastname)-1))))
where LastName = 'PIZER'

result I am getting on lastname is =  PIzer
I am expecting lastname to be Pizer

where have i gone wrong.
0
Question by:Amanda Walshaw
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 13

Assisted Solution

Koen Van Wielink earned 668 total points
ID: 39189847
Hi Flyfish,

When I run the exact script I get the expected result. Try this test script below:

``````Create table #temp
(lastname nvarchar(100))

insert into #temp
values ('PIZER')

Select lastname = Replace(Lastname,reverse(left(reverse(Lastname),len(Lastname)-1)),  reverse(lower(left(reverse(Lastname),len(Lastname)-1))))
from #temp

drop table #temp
``````

Rgds,

0

LVL 57

Assisted Solution

Raja Jegan R earned 664 total points
ID: 39189852
Try this which should work..

update dbo.mytable\$
set firstname = UPPER(substring(firstname, 1, 1)) + LOWER(substring(firstname, 2, LEN(firstname)))
,lastname = UPPER(substring(lastname, 1, 1)) + LOWER(substring(lastname, 2, LEN(lastname)))
where LastName = 'PIZER'
0

LVL 49

Accepted Solution

PortletPaul earned 668 total points
ID: 39189859
declare @lastname varchar(80) = 'PIZER'

select
lastname = substring(@lastname,1,1) + lower(substring(@lastname,2,80))
0

LVL 13

Expert Comment

ID: 39189888
Hi Flyfish,

Just wondering if you might have some spaces at the end of the name.
Try to put the lastname column between trim statements:

LTRIM(RTRIM(lastname))

By the way, I'm feeling really stupid seeing how complicated my method was to change the uppercase to lowercase....
0

LVL 9

Expert Comment

ID: 39189914
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
###### Suggested Courses
Course of the Month9 days, 4 hours left to enroll