[dba-SQLServer] SQL Nightmares

jwcolby jwcolby at colbyconsulting.com
Thu Mar 20 11:10:47 CDT 2008


Thanks A.D.  I try not to use Access for these databases simply because they
contain so many records.  VBA is interpreted and running stuff on 90 million
records would just overwhelm Access' capabilities I think.  I am doing this
straight in SQL Server 2005 and UDFs.

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 A.D.Tejpal
Sent: Thursday, March 20, 2008 11:43 AM
To: Discussion concerning MS SQL Server
Subject: Re: [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
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com




More information about the dba-SQLServer mailing list