how to change colum values in a trigger

Hi

Does anyone of you know how I can create a trigger to do the following

Table tbs contain 3 columns : total, num_col and alpha_col

I'm doing an insert in table tbs:
insert into tbs (total) values ('111aaa')

Now I want the trigger to split '111aaa' in to parts: An numeric and a characterpart. The trigger should store the numeric part (111) in column num_col. And the characterpart should be stored in alpha_col.

I'm working on a MS SQL Server 7

Can anyone help ?!?!?I am using sql server 2000. I don't know if INSTEAD OF TRIGGERS are available in that version. But here is a piece of code that I came up with at least to deal with the parsing of the string. This assumes that the numeric part is always at the beginning. You could use a cursor inside the trigger to process the info or if you don't want to use cursors you could write the parsing of the string as a function and use that in your SELECT statement for inserting into your table.

DECLARE @.vInput VARCHAR(20);
DECLARE @.vNum VARCHAR(10);
DECLARE @.vAlpha VARCHAR(10);
DECLARE @.vPosition INTEGER;
DECLARE @.vNumPos INTEGER;

SET @.vInput = '111aaa';
SET @.vNum = '';
SET @.vAlpha = '';

SET @.vPosition = 1;
WHILE @.vPosition <= DATALENGTH(@.vInput)
BEGIN
WHILE ASCII(SUBSTRING(@.vInput, @.vPosition, 1)) BETWEEN 48 AND 57
BEGIN
SET @.vNum = @.vNum + SUBSTRING(@.vInput, @.vPosition, 1);
SET @.vPosition = @.vPosition + 1;
END -- while number
SET @.vAlpha = @.vAlpha + SUBSTRING(@.vInput, @.vPosition,1);
SET @.vPosition = @.vPosition + 1;
END -- while string

print @.vNum;
print @.vAlpha;sql

How to calculate the total days between open and close date

Hi All,

I have a table call case and case_status have two fields, date and status as below:

date status

04/01/2006 open

04/05/2006 closed

04/10/2006 open

04/15/2006 closed

Whenever i open and closed the case, one record is insert into the case_status table.

Now I would need to calculate the total days of the case in storeprocedure.

Anyone can help me please.

Aung

This articledoes something similar. check if it helps.|||

One try:

CREATE

PROCEDURE [dbo].[caseDays]

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SETNOCOUNTON;RETURN(SELECTSUM(datediff( dd, c.openDate, d.closedDate))as myCaseDateFROM(SELECT a.cDateas openDate, row_NUmber()over(ORDERBY a.cDate)as ROWNUMBERFROM case_statusAS aWHERE(a.status='open'))as c

inner

join(SELECT b.cDateas closedDate, row_NUmber()over(ORDERBY b.cDate)as ROWNUMBERFROM case_statusAS bWHERE(b.status='closed'))as dON c.ROWNUMBER=d.ROWNUMBER)

END

I hope this one will be close to your solution.

Limno

|||

Thanks for your responsed.

But my problem is total days in two date between open and closed. I still facing this problem.

Thanks

Aung

|||

Hello:

datediff( dd,openDate,closedDate)

This function will give you how many days between open and closed days.

If this is not what you want, give a little more details about your problem.

Limno

|||Wouldn't the table also need a CaseID field so you know what case was being opened and closed?

How to calculate the index size ?

Hi:
I need to calculate the total size of all indexes, is the index_size
reported by sp_spaceused 'tablename' is what i need ?
Please help
Thanks
JCVoonThe following is a link to useful sps in what you are looking to do.
Specifically look at:
sp_columns_rowset
sp_MShelpindex
sp_MStablespace
sp_MSindexspace
In SQL Books there is an article called: Estimating the Size of a Table ,
which will give you some ideas
--
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1147078009.708121.91560@.i40g2000cwc.googlegroups.com...
> Hi:
> I need to calculate the total size of all indexes, is the index_size
> reported by sp_spaceused 'tablename' is what i need ?
> Please help
> Thanks
> JCVoon
>|||Jack Vamvas :
Thanks.
sp_MSindexspace is what i need, in fact the index_size reported by
sp_spaceused 'tablename' is sum of the Size reported by
sp_MSindexspace 'tablename' .
Regards
JCVoon

How to calculate the index size ?

Hi:
I need to calculate the total size of all indexes, is the index_size
reported by sp_spaceused 'tablename' is what i need ?
Please help
Thanks
JCVoonThe following is a link to useful sps in what you are looking to do.
Specifically look at:
sp_columns_rowset
sp_MShelpindex
sp_MStablespace
sp_MSindexspace
In SQL Books there is an article called: Estimating the Size of a Table ,
which will give you some ideas
--
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1147078009.708121.91560@.i40g2000cwc.googlegroups.com...
> Hi:
> I need to calculate the total size of all indexes, is the index_size
> reported by sp_spaceused 'tablename' is what i need ?
> Please help
> Thanks
> JCVoon
>|||Jack Vamvas :
Thanks.
sp_MSindexspace is what i need, in fact the index_size reported by
sp_spaceused 'tablename' is sum of the Size reported by
sp_MSindexspace 'tablename' .
Regards
JCVoon