trigger/string manipulation question

[fgeorge]

i have a field in my table that contains varchar data..all the data should begin with 0803465...


i now want to create a triger where i will trim the leading "0" and add 234..

so the number above becomes;
234803465...

can someone show me how to do this?

i need this done on all the records entered into that table so i guess i need a trigger..
thanks

 

[JamesLean]

I don't think you need a trigger. It sounds like you need to run a single update on your existing data and then if you insert new rows then insert them in the new format. Your update statement may look like this:

UPDATE tbl
SET col = '234' + RIGHT(col, LEN(col) - 1)
WHERE col LIKE '0%'

You would only need the WHERE clause if you had rows which didn't start with 0 and so didn't need to be updated.

--James

 

[fgeorge]

thanks jameslean,

i think i do need a trigger though as i want this transformation done when the data is inserted into the table..
i dont want my users entering 23480....
i want them to enter 0803...
but the info is to be stored as 234803...
how do i turn your code to a trigger?

thanks

 

[JamesLean]

You need an INSTEAD OF trigger:

CREATE TRIGGER tr_tbl_ins
  ON tbl
  INSTEAD OF INSERT
AS

INSERT tbl (col1, col2, col3)
SELECT col1, col2, '234' + RIGHT(col3, LEN(col3) - 1)
FROM inserted
GO

--James