function to trim leading zeros

Is there any function to remove leading zeros out of a string
for ex i need
001024314B1AXP to be conveted to 1024314B1AXP
Thanks
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:CBE08779-D394-431E-8AE6-CFAAAE0A4B5B@.microsoft.com...
> Is there any function to remove leading zeros out of a string
> for ex i need
> 001024314B1AXP to be conveted to 1024314B1AXP
> Thanks
>
It's not pretty, but you can take a look at CHARINDEX
Rick Sawtell
|||Hmmm. You could probably write one, but it wouldn't be too efficient in
SQL. Probably better to do it either before you store the data or in your
presentation layer. That said, here's a UDF to do it for you:
CREATE FUNCTION dbo.udf_StripLeadZeros (@.s VARCHAR(255))
RETURNS VARCHAR (255) AS
BEGIN
DECLARE @.i INTEGER
SET @.i = 1
DECLARE @.Done CHAR(1)
SET @.Done = 'F'
WHILE (@.i <= LEN(@.s)) AND (@.Done = 'F')
BEGIN
IF (SUBSTRING(@.s, @.i, 1) = '0')
SET @.i = @.i + 1
ELSE
SET @.Done = 'T'
END
RETURN SUBSTRING(@.s, @.i, LEN(@.s) - @.i + 1)
END
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:CBE08779-D394-431E-8AE6-CFAAAE0A4B5B@.microsoft.com...
> Is there any function to remove leading zeros out of a string
> for ex i need
> 001024314B1AXP to be conveted to 1024314B1AXP
> Thanks
>
|||Try this:
SELECT REPLACE(LTRIM(REPLACE( '001024314B1AXP' ,'0',' ')),' ','0')
Gert-Jan
Sanjay wrote:
> Is there any function to remove leading zeros out of a string
> for ex i need
> 001024314B1AXP to be conveted to 1024314B1AXP
> Thanks
|||On Wed, 20 Apr 2005 22:44:58 +0200, Gert-Jan Strik wrote:

>Try this:
>SELECT REPLACE(LTRIM(REPLACE( '001024314B1AXP' ,'0',' ')),' ','0')
Or this:
SELECT SUBSTRING('001024314B1AXP',
PATINDEX('%[^0]%', '001024314B1AXP'),
LEN('001024314B1AXP'))
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)