Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 363
  • Last Modified:

lower case on last name

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
Amanda Walshaw
Asked:
Amanda Walshaw
3 Solutions
 
Koen Van WielinkIT ConsultantCommented:
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
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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
 
PortletPaulCommented:
declare @lastname varchar(80) = 'PIZER'

select
  lastname = substring(@lastname,1,1) + lower(substring(@lastname,2,80))
0
 
Koen Van WielinkIT ConsultantCommented:
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
 
mimran18Commented:
Please use Datalength function instead of LEN function
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now