mickyT (2/27/2013)
However if you only have numbers in the string, I would probably go for a statement more like
UPDATE table.Statements
SET field03 = CAST(CAST(field03 as bigint) as varchar(20))
WHERE field03 like '0%'
Otherwise you could do
UPDATE table.Statements
SET field03 = SUBSTRING(field03,PATINDEX('%[^0]%',field03),20)
WHERE field03 like '0%'
Why? Please don't say "for performance reasons" until you've actually tested it. I tested all of the solutions offered so far and there's virtually no difference. Try it yourself. Here's a million row test table...
--===== Create and populate a large test table on-the-fly
SELECT TOP 1000000
Field03 = RIGHT('0000000000'+CAST(ABS(CHECKSUM(NEWID()))%2147483647+1 AS VARCHAR(10)),10)
INTO #TestTable
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
;
--Jeff Moden
Change is inevitable... Change for the better is not.