Greetings,
I'm new to SQL and am trying to cleanup the data that is in the ADD
REMOVE DATA table in SMS. I want the data to not have the time/date
stamps that were standard at one time in building SMS packages. We do
not put the time and date in the ADD Remove Data. Once I get this
clean SQL to work I want to create a view that can be used with the
SMS reporting tool (which can only manipulate views) to show what PCs
are not up to base standards and which PCs have non-standard data.

This is the Displayname00 column, table data  from
Add_Remove_Programs_DATA
----------------------------------------------------------------------------------------------------------------------------
COMTI Client Common 2002.7 6/5/2003 4:19:50 PM
Norton Location Pointer 11/4/2004 8:52:19 AM
ImageServices 2.9A 4/14/2003 8:39:59 AM
S/2 Foobar 04/04/05 04:05:12 PM
VHD for OS/2
NULL

My hack at trying to clean the Add Remove data
-----------------------------------------------------------------------------------------------------------------------------
SELECT (Rtrim(left(Displayname00,(CHARINDEX('/', Displayname00))-3)))
As Displayname0
FROM dbo.ADD_REMOVE_PROGRAMS_DATA
Where  (Right(Displayname00, 2) =
'AM' or Right(Displayname00, 2) = 'PM')
Union
Select Displayname00 as Displayname0
FROM dbo.ADD_REMOVE_PROGRAMS_DATA
WHERE (Right(Displayname00, 2) <> 'AM' AND
Right(Displayname00, 2) <> 'PM')


Error when run from S/2 Foobar row above

Server: Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring function.

Suggestions please to get it to run with out error.


Fred

Uh, why not doing such things in the client side

CREATE TABLE #temp
(
col VARCHAR(50)
)
INSERT INTO #temp VALUES ('COMTI Client Common 2002.7 6/5/2003 4:19:50 PM')
INSERT INTO #temp VALUES ('Norton Location Pointer 11/4/2004 8:52:19 AM')
INSERT INTO #temp VALUES ('ImageServices 2.9A 4/14/2003 8:39:59 AM')
INSERT INTO #temp VALUES ('S/2 Foobar 04/04/05 04:05:12 PM')
INSERT INTO #temp VALUES ('VHD for OS/2')



----My test
SELECT col,CHARINDEX('/', REVERSE(col)) AS pos,
RIGHT(col, CHARINDEX('/', REVERSE(col)))AS st
FROM #temp
WHERE RIGHT(col,2)IN('AM','PM')

-------If I remember well this UDF is written by Steve Kass

if exists (select * from dbo.sysobjects where [id] =
object_id(N'[dbo].[ReplaceEx]') and objectproperty([id],
N'IsScalarFunction') = 1)
   drop function [dbo].[ReplaceEx]
go

create function  [dbo].[ReplaceEx]
   (
   @_StringSource  varchar(8000),
   @_StringSearch  varchar(8000),
   @_StringReplace varchar(8000),
   @_ReplaceCount  int = 0, -- < 1 indicates that all occurrences are to be
replaced.
   @_ReplaceSkip   int = 0  -- < 1 indicates that no occurrences are to be
skipped.
   )
returns varchar(8000)
as
begin

   -- Validate the parameters.
   if (@_StringSource  is NULL) return NULL
   if (@_StringSearch  is NULL) return NULL
   if (@_StringReplace is NULL) return NULL

   -- Massage the parameters.
   select @_ReplaceCount = case when (isnull(@_ReplaceCount, 0) <= 0) then 0
else @_ReplaceCount end
   select @_ReplaceSkip  = case when (isnull(@_ReplaceSkip,  0) <= 0) then 0
else @_ReplaceSkip  end

   declare @StringReturn varchar(8000)
   select  @StringReturn = ''

   declare @LengthStringSource  int,
           @LengthStringSearch  int,
           @LengthStringReplace int
   select  @LengthStringSource  = datalength(@_StringSource), -- Note the
non-UNICODE implications with the DATALENGTH function.
           @LengthStringSearch  = datalength(@_StringSearch), -- Note the
non-UNICODE implications with the DATALENGTH function.
           @LengthStringReplace = datalength(@_StringReplace) -- Note the
non-UNICODE implications with the DATALENGTH function.

   declare @IndexStringSourceBegin int,
           @IndexStringSourceEnd   int
   select  @IndexStringSourceBegin = 0

   LabelLoop:
   select @IndexStringSourceEnd = charindex(@_StringSearch, @_StringSource,
@IndexStringSourceBegin)
   if (@IndexStringSourceEnd <> 0) begin
      -- Match found.
      select @StringReturn = @StringReturn + substring(@_StringSource,
@IndexStringSourceBegin, @IndexStringSourceEnd - @IndexStringSourceBegin)
      select @IndexStringSourceBegin = @IndexStringSourceEnd +
@LengthStringSearch
      if (@_ReplaceSkip > 0) begin
         -- Skipping matches.
         select @StringReturn = @StringReturn + @_StringSearch
         select @_ReplaceSkip = @_ReplaceSkip - 1
      end
      else begin
         select @StringReturn = @StringReturn + @_StringReplace
         if (@_ReplaceCount <> 0) begin
            select @_ReplaceCount = @_ReplaceCount - 1
            if (@_ReplaceCount = 0) begin
               -- Finished with desired number of replacements.
               goto LabelFinish
            end
         end
      end

      goto LabelLoop
   end

   LabelFinish:
   select @StringReturn = @StringReturn + substring(@_StringSource,
@IndexStringSourceBegin, @LengthStringSource - @IndexStringSourceBegin + 1)

   return(@StringReturn)

end
go




By using this UDF you can replace any occurence of the '/' to what you want
and then find out the position
of this charcter and remove all data after this position.

select
      dbo.ReplaceEx(
                   col,
                   '/',
                   '$',
                   1,
                   1
                   )
from #temp







<fstua***@REMOVE.maine.rr.com> wrote in message
Show quoteHide quote

Hi

Why not use the convert function in your view definition?

John


Show quoteHide quote

John,

What am I going to to convert with the convert function?  It is all
text data.

Fred



On Thu, 7 Jul 2005 05:27:03 -0700, John Bell
<jbellnewspo***@hotmail.com> wrote:

Show quoteHide quote

Hi

I didn't realise it was all one text string! Is therere not a separate
datetime field?

John

<fstua***@REMOVE.maine.rr.com> wrote in message
Show quoteHide quote

No datetime field to work with. I have come up with a
way to do this with Igor's UDF RCHARINDEX string function.
I can look from the right to find the 1st space starting at the 13th
character from right. Using that position I can then get the left part
of the column data.

Thanks for looking.
Fred



On Sun, 10 Jul 2005 16:13:54 +0100, "John Bell"
<jbellnewspo***@hotmail.com> wrote:

Show quoteHide quote

Inserting data to a table from other database. Help
Deadlock when not in transaction?
Using TIMESTAMP or DATETIME
Import XML
SQL statement puzzle