I have a table that I need to update 3 separate fields from one field in the same table. I have an address field that I split apart and now need to move the remaining info into the appropriate fields. This I am assuming can be done with a Case or If statement.
Here is an example of what needs to be done. The column labeled address needs to be copied to Add1, Add2 or Add3. However if there is info already in Add1 then it needs to move to the next column Add2 and so on. Any help would be greatly appreciated.

Thanks,
Cy

something like:

update table set
add1 = case
 when add1 is not NULL then add1
 else substring(address, 1, 10) -- do whatever splitting of first line here
end,
add2 = case
  when add1 is not null then substring(address, 1, 10) -- first line
  else substring(address, 11, 20) -- second line of address
end,
add3 = case
  when add1 is not null then substring(address, 11, 20) -- second line
  else substring(address, 21, 30) -- third line
end
where address = @address

Something like that except you wouldn't use substring, you would use another function to split it apart. I don't know what your data is like so I can't suggest one.

Brian

Some of the data has carriage returns and some does not. And there is not a set number of letters or numbers between each carriage return. For example this is one line of an address entry:
"Department of Information Science
Saga University
1 Honjo
Saga 840-8502"

Some entries have a normal address entry:

3201 E. Ft. Lowell Rd. # 2002

Thanks,
Cy

How do you parse the data, then? How would you identify what should go in add1 from these two samples?

Justin
Distributed Database Consulting, Inc.
http://www.ddbcinc.com/askDDBC

That will be tricky using just SQL. You may want to consider doing this at the application level, where you can do more heavy parsing.

Brian

How would i do that Brian?

Thanks,
Cy