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 call a function from a column formula in my MS SQL table

Good day!

What is the syntax on calling a function from a column formula in an MS SQL table.

I created a table, one column's value will be coming from a function. And at the same time, I will pass parameters to the function. How do I do this? Is this correct?

SELECT dbo.FunctionName([Parameter1, Parameter2])

But i can't save the table, "Error validating the formula".

Pls. help
Thanks a lot.<edit> Never mind, I misunderstood what you are doing.
I'm afraid I have no advice.|||It may or may not work depending on what you are trying to do. You can use a udf and define the result as simply:

DEFAULT (dbo.udfMyFunction('SomeParam','OtherParam'))

However, SomeParam and OtherParam must be constants or system functions (like suser_sname() or host_name()). They can't be names of columns in your table.

Regards,

hmscott|||Or perhaps he want a computed column:

create table foo (
id int,
hash as dbo.getHash(id),
...
);

How to Bulk Insert string data into a money colum?

How to Bulk Insert string data into a money colum?

Format files work great to import string data into a char column.

But I cannot convert a char column to either a money or numeric data type.

I get datatype errors when bulk inserting string data into a money column.

The string data is 22 characters long, no decimal point, trailing negative sign.

Here is an example: 000000000000007898384-

My goal: Bulk Insert the above sample string into a money column.

Often, for situations like this, it is useful to first import into a 'staging' table (same datatypes as import data),

AND then execute 'clean-up' routines

BEFORE moving the data into the production tables.

This allows correcting alot of data anomolies that cannot be handled with a format file.

|||

Thanks, Arnie, for the quick response. What clean-up routines did you have in mind?

Using Enterprise Manager to change the destination column's data type from "char" to "money" or "int" won't work.

Are you talking about concatenating a decimal point to the end of the source string, in hopes that the decimal point will help me get past the conversion issue?

|||

John,

Something like this could work for your situation:

Code Snippet

DECLARE
@.MyString varchar(25),
@.MyMoney money

SET @.MyString = '000000000000007898384-'

IF right( @.MyString, 1 ) = '-'
SET @.MyMoney = cast( ( '-' + left( @.MyString, ( len( @.MyString ) - 1 ))) AS money )
ELSE
SET @.MyMoney = cast( @.MyString AS money )

SELECT @.MyMoney

-7898384.0000

|||

So, it looks like you're putting the negative sign at the front as needed and leaving the positive numbers alone, before casting the result as money. This is fine.

My SQL table has 23 columns and millions of rows.

Column 10 is char, 22 characters wide. Negative signs are trailing.

How would I apply the code to all the rows of column 10 in that table?

|||

You would build a CASE structure (something like this):

DECLARE @.MyTable table
( RowID int IDENTITY,
Column10 varchar(50)
)

INSERT INTO @.MyTable VALUES ( '000000000000007898384-' )
INSERT INTO @.MyTable VALUES ( '000000000000007898385' )

SELECT
RowID,
MyMoney = cast( stuff( CASE
WHEN right( Column10, 1 ) = '-'
THEN ( '-' + left( Column10, ( len( Column10 ) - 1 )))
ELSE Column10
END
, len( CASE
WHEN right( Column10, 1 ) = '-'
THEN ( '-' + left( Column10, ( len( Column10 ) - 1 )))
ELSE Column10
END ) -1
, 0, '.' ) AS money )
FROM @.MyTable

RowID MyMoney
--
1 -78983.8400
2 78983.8500

For illustration purposes, I've also added a decimal point two places from the right. If you don't need that, then remove the STUFF() function. (I thought that just 'might' come up next...)

You 'should' be able to use a query like this to INSERT the staging table data into your production table.

|||

Ok, Arnie, I'll give it a shot. Thanks again.

John

|||

My brain wasn't operating on all cylinders this morning.

You can do without the STUFF() function -just divide by 100 if you need the partial money, remove the [ / 100 ] if you have whole money.

Code Snippet

SELECT
RowID,
MyMoney = cast( CASE
WHEN right( Column10, 1 ) = '-'
THEN ( '-' + left( Column10, ( len( Column10 ) - 1 )))
ELSE Column10
END AS money ) / 100

FROM @.MyTable

|||This is great, Arnie, thanks.