[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
>
>