In a SQL Server table I have the following values and
I would like to have

055Z84 -> 55Z84

055t84 -> 5584

65588H -> 65588

A55804 -> 55804

-- Create a sample table
CREATE TABLE YourTable (
    YourColumn VARCHAR(50)
);

-- Insert sample data
INSERT INTO YourTable (YourColumn) VALUES
('055Z84'),
('055t84'),
('65588H'),
('A55804');

-- Update the table to remove leading zeros and alphabetical characters
UPDATE YourTable
SET YourColumn = 
    CASE 
        WHEN YourColumn LIKE '%[a-zA-Z]%' THEN 
            REPLACE(SUBSTRING(YourColumn, 
                              PATINDEX('%[1-9]%', YourColumn), 
                              LEN(YourColumn) - PATINDEX('%[1-9]%', YourColumn) + 1),
                    '0', '')
        ELSE 
            CASE WHEN YourColumn <> '0' 
                 THEN LTRIM(REPLACE(YourColumn, '0', ''))
                 ELSE '0' 
            END
    END
WHERE YourColumn LIKE '%[a-zA-Z]%' OR YourColumn LIKE '0%';

-- View the updated table
SELECT * FROM YourTable;