Monday, March 2, 2009

Power of SQL: Update Replace()

Sometimes you realize after the fact that there's an issue with the way your data is being stored. In this case, we had a field that was storing names. Originally intended to be able to show an admin / show on reports the full person's name without concatenating data together, here is what was being built.
"firstname middlename lastname"
Unfortunately, when the middlename was not filled out, it contained two spaces between the first and last names. In addition to fixing the way that the the data is being updated, there's an easy fix for existing data.

update users
set fullname = replace(fullname,'  ',' ')
where fullname like '%  %'
For clarity, this finds and replaces two spaces with one space. Its easy to adapt to your specific situation and yes, its really that easy.

No comments: