Firstnames

issues with middle name

in one column I  have a firstname and a middle name

Gwenda agnes
Pamela joyce
Jocelyn ruth
Gregory ian
Cheryl joy

and sometimes  just one name
John

Where you have two names in the firstname column how do you get it to appear as

Gwenda Agnes

If there is only one space between the next name?
Amanda WalshawBusiness Solutions AnalsystAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Anthony PerkinsConnect With a Mentor Commented:
Your parenthesis are all wrong in comparison to the solution posted.  This is more like the solution posted (no points please):
UPDATE  dbo.mytable
SET     firstname = REPLACE(Firstname, REVERSE(LEFT(REVERSE(firstname), LEN(firstname) - 1)) + ' ' + REVERSE(LOWER(LEFT(REVERSE(firstname), LEN(firstname) - 1))))
WHERE   Firstname = 'Kym dianne'
        AND LastName = 'Spoors'

Open in new window


Caveat: I have no idea if this accomplishes your task (I would never use T-SQL for this) I am merely correcting the syntax.
0
 
santoshmotwaniCommented:
hope this helps :

link
0
 
Amanda WalshawBusiness Solutions AnalsystAuthor Commented:
sorry page not found
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
PortletPaulfreelancerCommented:
http://www.devx.com/tips/Tip/17608 (worked for me)

It's an "Initial Capitals" function
0
 
Amanda WalshawBusiness Solutions AnalsystAuthor Commented:
sorry its taking me to an advertisment about registering in San francisco
0
 
Amanda WalshawBusiness Solutions AnalsystAuthor Commented:
-update Staging_mytable
set firstname = Replace(Firstname,reverse(left(reverse(firstname),len(firstname)-1)),  reverse(lower(left(reverse(firstname),len(firstname)-1))))      ,


I have used this query but I am getting struck by the middle name, it is consistent with one space.
0
 
santoshmotwaniCommented:
i have copied the script into .txt file from the link .... its a same link that PortletPaul has posted.
notepad.txt
0
 
santoshmotwaniConnect With a Mentor Commented:
do you want to try this :

update Staging_mytable
set firstname = Replace(Firstname,reverse(left(reverse(firstname),len(firstname)-1)) + ' ' + reverse(lower(left(reverse(firstname),len(firstname)-1))))
0
 
Amanda WalshawBusiness Solutions AnalsystAuthor Commented:
update dbo.mytable
set firstname = Replace(Firstname,reverse left(reverse(firstname),len(firstname)-1)))) + ' ' + reverse(lower(left(reverse(firstname),len(firstname)-1)))
where Firstname = 'Kym dianne'
and LastName = 'Spoors'

unfortunately getting error message
The replace function requires 3 argument(s).
0
 
Anthony PerkinsCommented:
So much for "no points please".  Oh well ...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.