Using your example:

USE DevTestDB;

GO

CREATE TABLE #Emp (ID INT IDENTITY(1,1),

TNum VARCHAR(12),

NOMB1 VARCHAR(8));

INSERT INTO #EMP (Tnum, NOMB1)

VALUES ('0011008183', '02020790'),

('0011008183', '00000000');

--Your Statement

select SUBSTRING(Emp.NOMB1, PATINDEX('%[^0]%', Emp.NOMB1+'.'), LEN(Emp.NOMB1)) as EmpNUM

from #Emp Emp where TNum = 0011008183 --Why is this now an INT? It has preceding 0's.

DROP TABLE #Emp;

Your statement isn't correct, the statement bring back TWO rows, '2020790' & ''. No row is very different to a row containing a blank value.

We still need that DDL and sample data though.