format data before being inserted into a column

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

deheinz1 - 23 Mar 2005 22:55 GMT

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

Reply to this Message

MPS - 23 Mar 2005 23:10 GMT

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

>I am trying to manipulate data before it is inserted into a column. For
> instance
[quoted text clipped - 11 lines]
>
> Dave

Reply to this Message