Solved

lower case on last name

Posted on 2013-05-22
5
340 Views
Last Modified: 2013-05-22
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
Comment
Question by:Amanda Walshaw
5 Comments
 
LVL 12

Assisted Solution

by:Koen Van Wielink
Koen Van Wielink earned 167 total points
Comment Utility
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

Open in new window


Rgds,

Kvwielink
0
 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 166 total points
Comment Utility
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 48

Accepted Solution

by:
PortletPaul earned 167 total points
Comment Utility
declare @lastname varchar(80) = 'PIZER'

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

Expert Comment

by:Koen Van Wielink
Comment Utility
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

by:mimran18
Comment Utility
Please use Datalength function instead of LEN function
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now