how to update only first character in SQL Server

Question

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

Answer 1

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.

Answer 2

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%'

Answer 3

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