Can't see the forest for the trees
Posted on 2012-03-20
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
set middle_name = substring(first_name,
(select patindex('% %',rtrim(first_name))) + 1,
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