manipulating varchar fields

[fgeorge]

i get people to enter numbers into a particular field..
eg. 2345678..
how can i automatically remove the first digit of what they enter and replace it with +234?
thanks..

 

[Strannik]

I think it'd be better implement on client side ... and pass replaced string to MSSQL.
Or else design MSSQL instead trigger to dynamically replace certain field ..

 

[fgeorge]

can u give me an example of an instead triger i can use for this example?

 

[Strannik]

create table MyPoorTable(ID int,StringValue nvarchar(255))

CREATE TRIGGER trInsertData ON MyPoorTable
INSTEAD OF INSERT
as
INSERT INTO MyPoorTable(ID,StringValue)
SELECT ID,STUFF(StringValue,1,1,'+') FROM INSERTED

insert into MyPoorTable(ID,StringValue) VALUES(1,'123')

select * from MyPoorTable

 

[fgeorge]

this is an example...
i want to convert the number 08032345678
to +2348032345678.
thanks..

 

[JayKusch]

Here is a try ...


CREATE TRIGGER Append_234
ON MyTable
FOR Insert, Update
AS
SET Nocount ON

UPDATE MyTable
SET MyField = '+234' + SUBSTRING(MyField,2,40)
FROM MyTable
JOIN Inserted
ON MyTable.TableKey = Inserted.TableKey

SET Nocount OFF


Of course we need a couple of things in place for this to happen. One would be that the table has a PK, TableKey, for the Inserted table to be referenced. Two, change the 40 in the SUBSTRING to the total length of the field, MyField.

Is this more in line as to what you are attempting?

Thanks

J. Kusch

 

[JayKusch]

OOPS ... one more item that also brings up another question. I have the example trigger set as an Insert/Update. It should only be for Insert, so drop the Update portion.

Question/comment is ... will the user be able to update the field once it has been instered? If so, the trigger will not fire and the new value they entered will not be prefixed with the +234. It gets rather tricky.

If this is the case, you may want to readdress Stranniks' idea of completing this transformation on the client side and then placing it into the table.

Thanks

J. Kusch

 

[Strannik]

to JayKusch:

1.You forgot inserted prefix:<b>SUBSTRING(inserted.MyField,2,40)</b>

2.What about following batch:

insert into MyTable(TableKey,MyField) values(1,'22222')
update MyTable set TableKey = 2 WHERE TableKey = 1

MyField would be replaced two times but second time is not need because it's not affected in the UPDATE statement.

 

[Strannik]

to JayKusch:

1.You forgot inserted prefix:SUBSTRING(inserted.MyField,2,40)

2.What about following batch:

insert into MyTable(TableKey,MyField) values(1,'22222')
update MyTable set TableKey = 2 WHERE TableKey = 1

MyField would be replaced two times but second time is not needed because MyField field is not affected in the UPDATE statement.

 

[JayKusch]

You do not need the inserted prefix.

Thanks

J. Kusch