Go Premium for a chance to win a PS4. Enter to Win

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

Can't see the forest for the trees

I have the following table (3500 rows)

Some (most) first name fields also contain middle name or initial


Last Name        First Name                          Middle Name                       Address 1
Peone               Nicholas Kane                            NULL                                      1435 Emma Lane
Russo               Joseph J.                                    NULL                                       347 6th Ave., #3
McDonnell      Sarah Wimsatt                            NULL                                       180 L St., #2
Carvalho              Michael Paul                            NULL                                      2168 Major Loring Way
Gildea              Caitlin                                   Elizabeth                              549 Columbus Ave., #4
Lowe              Justin J.                                   NULL                                     AGO-CPD
Welch             William M.                           NULL                                     25 Meadowbrook Rd.
Adams             Sandra                                   NULL      57 Fitchburg Rd.


I am trying to extract the middle name/initial an stuff it into the middle name field.

Once I know that is correct I'll truncate first name at the space

This is the code I am using



       update newlawyers2011
            set middle_name = substring(first_name,
                  (select patindex('% %',rtrim(first_name))) + 1,
                  len(rtrim(first_name))
      where (select patindex('% %',rtrim(first_name)) > 0

Fails with error-

Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'where'.
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near '>'.


I have been staring at this for 2 hours and I can't read the letters any more.
Any help would be appreciated
0
Jeff_Kingston
Asked:
Jeff_Kingston
  • 2
  • 2
1 Solution
 
BuggyCoderCommented:
Do This:-

update newlawyers2011 set middle_name =
substring(first_name,patindex('% %',rtrim(first_name)) + 1,len(rtrim(first_name))
where (patindex('% %',rtrim(first_name)) > 0
0
 
Jeff_KingstonAuthor Commented:
Now get this error


Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'where'.
0
 
TempDBACommented:
Little modification to BuggyCoder solution

update newlawyers2011 set middle_name =
substring(first_name,patindex('% %',rtrim(first_name)) + 1,len(rtrim(first_name))
where (patindex('% %',rtrim(first_name))) > 0
0
 
BuggyCoderCommented:
Thanks TempDBA i generally mess up with braces...
You are a life saver....
:-)
0
 
Jeff_KingstonAuthor Commented:
Thanks saved the day
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

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