Update table and add new column with check for all rows for UserName numbers | SQL Server

I need add this script

ALTER TABLE [dbo].[MyTable]
    ADD [MyNewColumnCheckUserName] BIT NOT NULL DEFAULT 0;

But after the update, I need to check all old rows in MyTable – all UserName (it is column in this table) if UserName has only numbers? If yes – set true for this row MyNewColumnCheckUserName.

Answer

You can use:

update mytable
    set MyNewColumnCheckUserName = (case when UserName like '%[^0-9]%' then 0 else 1 end);

However, I would recommend just adding this as a computed column:

alter table mytable add MyNewColumnCheckUserName as 
    (case when UserName like '%[^0-9]%' then 0 else 1 end);

There is no reason that I know of to prefer a bit instead of a number for a computed column. But if you really want to incur the overhead of a bit, you can convert the value:

alter table mytable add MyNewColumnCheckUserName as 
    (convert(bit, (case when UserName like '%[^0-9]%' then 0 else 1 end));