[dba-SQLServer] SQL Nightmares

John,

    In access desktop, the following expression will strip leading zeros from a digital string, leaving the trailing zeroes intact (HouseNum is the text type field carrying the string to be processed):

    Mid([HouseNum],(InStr([HouseNum],Replace([HouseNum],"0",""))))

    Sample SQL for displaying the stripped values (as HNum) along with the original ones, would be as follows (T_JC is the name of data table):
======================================
SELECT T_JC.HouseNum, Mid([HouseNum],(InStr([HouseNum],Replace([HouseNum],"0","")))) AS HNum FROM T_JC;
======================================

    Typical results:
    ---------------
    HouseNum                     HNum
    00001200                      1200
    00000123000                123000

Best wishes,
A.D.Tejpal
------------

  ----- Original Message ----- 
  From: jwcolby 
  To: 'Discussion concerning MS SQL Server' 
  Sent: Thursday, March 20, 2008 07:41
  Subject: Re: [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