Concatenate fields based on query

[bryandj23]

Hi All.

I have a SQL 2005 database containing customer information (Table: Customer). Years ago, the front-side application used to manage this database only allowed a minimal amount of characters for the FirstName field. At that time, we used the MidName field to continue family names into our records:

FirstName          MidName         LastName
-------------------------------------------
John               & Jody          Smith
Tyrone             & Knotty        Shoelaces
Humphrey           & Gisabell      Nutt

If trying to write code to move any middle name starting with "&" to then end of the FirstName field, and change the MidName field to NULL, so that I have:

FirstName           MidName         LastName
--------------------------------------------
John & Jody         NULL            Smith
Tyrone & Knotty     NULL            Shoelaces
Humphrey & Gisabell NULL            Nutt

I figured I'd have to place FirstName and MidName into variables, but am unsure how to update a record based on the output of said record.

I had some backwards code that did in fact work, but it only did one customer per run of the code.

So, basically:

1. If MidName field starts with "&"
2. then make FirstName = Firstname + " " + MidName
3. make MidName = NULL

Any help would be greatly appreciated. THANKS!

 

[r937]

UPDATE Customer
   SET FirstName = Firstname + " " + MidName
     , MidName = NULL
 WHERE LEFT(MidName,1) = '&'

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[bryandj23]

DOH! Do I feel like a tool.

Here I was declaring variables, doing "Like" queries, and all other sorts of stuff, when I didn't even consider such a simplistic approach. Doh!

Thanks r937.