delete first five zeroes in an accountnumber in sql server

select right('000001020304043575',len('000001020304043575')-5)

Just in case it's not always 5 zeroes and you just want to remove leading zeroes...

DECLARE @AcctNumber      VARCHAR(50) = '000001020304043575'

SELECT SUBSTRING(@AcctNumber, PATINDEX('%[^0]%', @AcctNumber), LEN(@AcctNumber))

sorry not delete, display  '000001020304043575' as 1020304043575

If you want to delete this value from table, you have to update the table.  But first test this in test table before implement on production table.

update tablename
set colname =right(AccountNOColumn,len(accountNoColumn)-5)

Can I use cast

If you want it to be a number rather than a string, you can certainly cast it.

Actually, I just realized the whole size of the accountnumber is varchar(25). However, the accountnumber can be 16 digits or 17 digits and rest are leading zeros. How do I trim leading zeros in this case.

Brian Crowe already showed you how to do that by taking the substring starting at the first non-zero character, which could then be cast to numeric.

How can I use cast to display removing leading zeros, please help !

You've already gotten that answer.  You haven't provided a query or any schema information so you got a generic usage.

I think I can use cast(cast(AccountNumber as bigint) as varchar)

That could work depending on the size of the account number.  You would have to test to see if it is the most performant.

I'd offer one very slight mod to Brian's response, just on the off chance that the whole acct number is zero.  Naturally NO points for me please:

SELECT SUBSTRING(@AcctNumber, PATINDEX('%[^0]%', @AcctNumber + ' '), LEN(@AcctNumber))