Solved

# Firstnames

Posted on 2013-05-23
Medium Priority
175 Views
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?
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
• 4
• 3
• 2
• +1

LVL 16

Expert Comment

ID: 39193074
hope this helps :

0

Author Comment

ID: 39193107
0

LVL 49

Expert Comment

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

It's an "Initial Capitals" function
0

Author Comment

ID: 39193149
0

Author Comment

ID: 39193169
-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

LVL 16

Expert Comment

ID: 39193172
i have copied the script into .txt file from the link .... its a same link that PortletPaul has posted.
0

LVL 16

Assisted Solution

santoshmotwani earned 1000 total points
ID: 39193182
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

Author Comment

ID: 39193276
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

LVL 75

Accepted Solution

Anthony Perkins earned 1000 total points
ID: 39196726
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'
``````

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

LVL 75

Expert Comment

ID: 39199392
So much for "no points please".  Oh well ...
0

## Featured Post

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be \$37.1B.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages â€“ plus untold reputational damage to one of the worldâ€™s most trusted airlines. All due to a catastrophâ€¦
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
###### Suggested Courses
Course of the Month10 days, 23 hours left to enroll