[dba-SQLServer] SQL Nightmares

I don't know.  It needs to leave the trailing zeros.  IOW

000120 = 120 

NOT 12 

REPLACE in VB simply replaces every occurrence of a zero, not what is needed
here.

John W. Colby
Colby Consulting
www.ColbyConsulting.com 
-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Arthur
Fuller
Sent: Wednesday, March 19, 2008 7:43 PM
To: Discussion concerning MS SQL Server
Subject: Re: [dba-SQLServer] SQL Nightmares

Here you go:

<code>
-- ================================================
-- Template generated from Template Explorer using:
-- Create Scalar Function (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the function.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        Arthur Fuller
-- Create date: 19-Mar-08
-- Description:    Strip leading zeroes from a char value
-- =============================================
CREATE FUNCTION StripZero
(
    -- Add the parameters for the function here
    @Value_str varchar(1000)
)
RETURNS varchar(1000)
AS
BEGIN
    DECLARE @Result varchar(1000)
    SELECT @Result = Replace(@Value_str,'0','')
    RETURN @Result
END
GO
-- sample call:
select dbo.stripzero('000123fgh')
-- returns
-- 123fgh
</code>

Will that do, JC?
A.

On 3/19/08, jwcolby <jwcolby at colbyconsulting.com> wrote:
>
> I have another (different) database from hell.  This one has data 
> fields like
>
> HOUSENUMBER             StreetName              StreetUnit
> 00000006                000012th                Place
>
> As you can see, if I do a simple append, then I get something like
>
> 00000006 000012th Place.
>
> I can tell you that won't fly.  So I need to strip off leading zeros, 
> pretty much in ALL my fields, but at the very least in a fixed set of 
> fields.  How do I do this in SQL, bearing in mind that this table has 
> 90 million records and taking a week for one field is out of the 
> question.
>
> John W. Colby
> Colby Consulting
> www.ColbyConsulting.com
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com