format data before being inserted into a column

I am trying to manipulate data before it is inserted into a column. For
instance
I am reading in a MAC addreess which looks like '00DE0B16AA99' and when I
do an insert into the column I want it to be '00:DE:0B:16:AA:99' In other
words I am trying to insert the semicolons. I thought I was on to something
with the sp_bindrule but have not had sucess with it yet. I am pretty sure
that you can set up some sort of formattting on a column maybe when creating
the table but have not figured it out. Any help is appreciated.
Thanks in Advance
Dave
Probably you can create a User Defined Function (named MyFunction, that
takes a string as parameter and generates a string as result) in that SQL
database, in order to convert the string from
00DE0B16AA99
to
00:DE:0B:16:AA:99
I think that a SQL sentence like
Insert into TABLE values (... MyFUnction('00DE0B16AA99')
and MyFUnction should be like:
CREATE FUNCTION dbo.MyFunction (@.MAC varchar(12))
RETURNS varchar(17)
AS
BEGIN
declare @.NEWMAC varchar(17)
...
...your conversion code here
...
return (@.NEWMAC)
END
Hope it helps
Michael Prendergast
"deheinz1" <deheinz1@.discussions.microsoft.com> escribi en el mensaje
news:8F622015-658D-4C83-8F85-6B4797FB78AE@.microsoft.com...
>I am trying to manipulate data before it is inserted into a column. For
> instance
> I am reading in a MAC addreess which looks like '00DE0B16AA99' and when I
> do an insert into the column I want it to be '00:DE:0B:16:AA:99' In other
> words I am trying to insert the semicolons. I thought I was on to
> something
> with the sp_bindrule but have not had sucess with it yet. I am pretty
> sure
> that you can set up some sort of formattting on a column maybe when
> creating
> the table but have not figured it out. Any help is appreciated.
> Thanks in Advance
> Dave
>