how to update only first character in SQL Server

问题:

I have follwoing data in MyTable

ID = 1, No1 = 23, No2 = 45, D = 1
ID = 2, No1 = 12, No2 = 5, D = 1
ID = 3, No1 = 14, No2 = 4, D = 1
ID = 4, No1 = 41, No2 = 12, D = 1
ID = 5, No1 = 2, No2 = 25, D = 1
ID = 6, No1 = 3, No2 = 96, D = 1

I want to update only first character of column No1 if it is 1 then it should be 0 as following

ID = 1, No1 = 23, No2 = 45, D = 1
ID = 2, No1 = 02, No2 = 5, D = 1
ID = 3, No1 = 04, No2 = 4, D = 1
ID = 4, No1 = 41, No2 = 12, D = 1
ID = 5, No1 = 2, No2 = 25, D = 1
ID = 6, No1 = 3, No2 = 96, D = 1

Now I am using C# to do this, I Open all records of No1 From MyTable and checking in C# manually using loop if I found first character as 1 then I make string and update database .. and it is working but problem is I have 10000 X 100 Records in Mytable so every time loop will run 10000 X 100 times and result is application low performance, I just want to ask is "there is any method to update Only fist character of a No1 coloumn by using only SQL and with C#...

Update : All datatypes of MyTable are int

回答1:

If your Datatype for No1 is INT, then if you find and replace value begins with 1, SQLServer will store the value as 2 in case of 12 not as 02.

update #MyTable set No1 = case when left(No1,1) = 1 then stuff(NO1,1,1,0) else No1 end  --Update

回答2:

Given we know the value ranges from 0 to 9999, the query could look like:

update MyTable
set No1 = CASE WHEN No1 between 10 and 19     THEN No1 - 10
               WHEN No1 between 100 and 199   THEN No1 - 100
               WHEN No1 between 1000 and 1999 THEN No1 - 1000 END
where No1 between 10 and 19    
   or No1 between 100 and 199  
   or No1 between 1000 and 1999

If its run really often, you may need to look at adding appropriate indexes and so on, but a quick test suggests its not too bad.

回答3:

As per @chrisb Old Answer it is worked on nvarchar(MAX),

UPDATE MyTable
SET No1 = '0' + SUBSTRING(No1, 2, LEN(No1) - 1)
WHERE No1 LIKE '1%'