| Newbie | | Join Date: Jul 2007
Posts: 7
| |
- SET QUOTED_IDENTIFIER OFF
-
GO
-
SET ANSI_NULLS ON
-
GO
-
-
-
-
ALTER Function dbo.ZeroFill_ObjectNumber (@ObjectID int,@ObjectNumber char(42), @SortNumber char(56),@ComponentNumber varchar(42))
-
RETURNS Varchar(2000) AS
-
-
BEGIN
-
-
declare @stringvar as varchar(255)
-
-
declare cur CURSOR FOR select Objects.ObjectId, Objects.ObjectNumber, Objects.SortNumber, objComponents.ComponentNumber
-
from Objects, ObjComponents
-
where ObjectLevelID = '6'
-
and objectnumber like 'GR%'
-
and objectnumber not like 'GR[0-9][0-9][0-9][0-9]'
-
and objectnumber not like 'GR[0-9][0-9][0-9][0-9][.][0-9][0-9][0-9][0-9]%'
-
and objectnumber not like 'GR[0-9]'
-
and Objects.ObjectId = ObjComponents.ObjectId
-
-
OPEN cur
-
set @stringvar = ''
-
-
fetch next from cur into @stringvar
-
While @@fetch_status = 0
-
BEGIN
-
IF @stringvar Like '%.[0-9][0-9]/%'
-
Begin
-
set @stringvar = substring(@stringvar,1,patindex('%.%',@stringvar)) + '00' +substring(@stringvar,patindex('%.%',@stringvar)+1 ,len(@stringvar))
-
-
End
-
Else If @stringvar Like '%.[0-9][0-9][0-9]/%'
-
Begin
-
set @stringvar = substring(@stringvar,1,patindex('%.%',@stringvar)) + '0' +substring(@stringvar,patindex('%.%',@stringvar)+1 ,len(@stringvar))
-
-
End
-
-
fetch next from cur into @Stringvar
-
-
END
-
CLOSE cur
-
DeAllocate cur
-
-
return @stringvar
-
-
END
-
-
-
GO
-
SET QUOTED_IDENTIFIER OFF
-
GO
-
SET ANSI_NULLS ON
I need to update the objectnumber once I have zero filled. I think this is a good function could use some help finishing.
|
