I'm trying to remove the first character in LastNames that start with a space. The code below seems to clear LastName entirely, making the field empty. Do you see something wrong?
DECLARE @Id int
DECLARE @LastName NVARCHAR(255)
while (SELECT COUNT(*) FROM [Table] WHERE LastName LIKE ' %') > 0
Begin
Select Top 1 @Id = Id, @LastName = LastName FROM [Table] WHERE LastName LIKE ' %'
UPDATE [Table] SET LastName = SUBSTRING(@LastName, 1, LEN(@LastName)) WHERE Id = @Id
End
For me the code you posted runs in an infinite loop.
LastName = SUBSTRING(LastName, 1, LEN(LastName)) will have no effect other than removing right trailing space (as that is not counted by LEN). To remove the left most character you would need SUBSTRING(LastName, 2, LEN(LastName) - 1).
But, you don't need a RBAR (Row By Agonizing Row) loop here just use
UPDATE [Table]
SET LastName = LTRIM(LastName)
WHERE LastName LIKE ' %'
to update all matching rows in a set based way.
I don't quite understand why you're using a LIKE '°%' statement in the SELECT statement, while restricting. Next to that, you should avoid executing queries inside a loop, since that will hurt performance badly.
SQL is set based , so it is perfectly possible to execute this code without using a loop.
UPDATE [Table]
SET LastName = LTRIM(LastName)
WHERE LastName LIKE ' %'
LTRIM is needed here. The OP's code will process the same row multiple times if it has more than one leading space (and LastName = SUBSTRING(LastName, 1 LEN(LastName) will not have the desired effect anyway)