cleaning values

is there a quick way to do this within sql.

i am thinking of something along the lines of what would be an equivalent to something in PHP (obviously not a rdbms) like
foreach ($ArrayName as $varName=>$value)
{
$value = rtrim(ltrim($value));
}

instead of having to do each variable manually.

Thanks for the help

One reason this will not work is because TSQL variables are strong typed. You would error out if you attempted this with a date or number variable.
One option is to create a function that contains all the variables called resetVariables that does nothing but reset the variables.

thanks for the help. I was hoping there was a quick way.

Another reason is MS-SQL has no concept of arrays. You would have to use cursors and it would be slow and ugly, and like victor pointed out you would run into issues with strongly typed columns.

If you are using varchar fields they will be automatically trimmed. If you are using char fields they will contain leading and trailing spaces.

If you are storing variable width data use a varchar and trim it on the way in through a sproc. Then you do not need to trim them on the way out. Otherwise just trim the columns on the way out in a select..

You can also update a table and trim the values, however this will do no good if they are stored type char.

I might have misunerstood your question. You can do a mass update to all the rows in a table with an update query.

This will trim all the rows in the column named stat in the fooBar table.Careful... If you do not use a where clause you could update some data you don't want to.The following example uses a where clause to limit the update to rows with a col1 value of 3.

Adam