This script will remove all leading zeros from a string and shows actual value. This also handles if the string starts with the text and still does have leading zeros in between.
--Example 1: - Starts with Zero
--=========
declare @String varchar(30)
select @String = '00001234'
select case when ISNUMERIC(@String) = 0 then SUBSTRING (@String,0,CHARINDEX('0',@String)) + SUBSTRING(REPLACE(@String,SUBSTRING (@String,0,CHARINDEX('0',@String)),''), PATINDEX('%[^0]%', REPLACE(@String,SUBSTRING (@String,0,CHARINDEX('0',@String)),'')+'.'), LEN(REPLACE(@String,SUBSTRING (@String,0,CHARINDEX('0',@String)),''))) else SUBSTRING(REPLACE(@String,SUBSTRING (@String,0,CHARINDEX('0',@String)),''), PATINDEX('%[^0]%', REPLACE(@String,SUBSTRING (@String,0,CHARINDEX('0',@String)),'')+'.'), LEN(REPLACE(@String,SUBSTRING (@String,0,CHARINDEX('0',@String)),''))) end
GO
--Example 2: - Starts with Text but still has leading zeros
--=========
declare @String varchar(30)
select @String = 'TEST00001234'
select case when ISNUMERIC(@String) = 0 then SUBSTRING (@String,0,CHARINDEX('0',@String)) + SUBSTRING(REPLACE(@String,SUBSTRING (@String,0,CHARINDEX('0',@String)),''), PATINDEX('%[^0]%', REPLACE(@String,SUBSTRING (@String,0,CHARINDEX('0',@String)),'')+'.'), LEN(REPLACE(@String,SUBSTRING (@String,0,CHARINDEX('0',@String)),''))) else SUBSTRING(REPLACE(@String,SUBSTRING (@String,0,CHARINDEX('0',@String)),''), PATINDEX('%[^0]%', REPLACE(@String,SUBSTRING (@String,0,CHARINDEX('0',@String)),'')+'.'), LEN(REPLACE(@String,SUBSTRING (@String,0,CHARINDEX('0',@String)),''))) end 
Write for SQLServerCentral - we're looking for new writers for SQL Server articles, scripts and questions.
2019-03-25
9,342 reads
Redgate (which means Red Gate Software Limited and its subsidiary Red Gate Software Inc.) respects your privacy. All information you give us is held with the utmost care and security. Please take time to review this privacy policy as it sets out our privacy practices and tells you how your personal information will be treated […]
2019-03-25
44,891 reads
It depends. The mantra of many DBAs and others in IT. Steve Jones reminds us why it applies.
2020-06-23 (first published: 2009-03-25)
539 reads
Encryption in SQL Server is difficult to implement and manage, but it is being required more and more often. However understanding what encryption means is as important as being able to manage and implement it. Michael Coles brings us part 2 in his series on explaining the mysteries behind cryptography.
2005-08-17
11,003 reads
I was talking with the people over at Idera a few weeks ago and they mentioned that their CEO would be happy to give us a few minutes of his time. So I took them up on the offer and built a list of questions about Idera and SQL Server 2005. Take a moment to learn about the vendor view of the world and the impending release of SQL Server 2005.
2005-03-15
6,097 reads