Add 0 in front of all numbers in field

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Add 0 in front of all numbers in field

Author

Topic

hummy
Starting Member

32 Posts

Posted - 2008-12-01 : 11:37:43

Hi,

We have a file which we import using a third party app into one of our existing systems. The problem is that the file we recive omits the first 0 off all mobile numbers and so when we look at the imported data the number is incorrect and requires manually to add the 0 in.

Can someone please help me to create a trigger that adds a 0 in front of my numbers at the point of import The field is called Mobile.

If it was Oracle i'd know how to do it using oracle specfic language not sure how to do it in sql.

Thanks

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-12-01 : 11:41:55

in trigger:
update t1
set mobile = '0' + mobile
from yourTable t1 join inserted i on t1.id = i.id

or you can just update it like this based on the date of import...

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.1 out!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-01 : 12:39:01

I agree with Spirit.
A one-time update will do fine.

UPDATE Table1
SET Col1 = '0' + Col1
WHERE Col2 >= '20080101'
AND Col2 < '20090101'
AND Col1 LIKE '[^0]%'

E 12°55'05.63"
N 56°04'39.26"

hummy
Starting Member

32 Posts

Posted - 2008-12-01 : 13:48:16

thanks for your responses. But i don't think a one time update will do. Because we do this import every week having been provided the file from a third party. So every week the mobile field is missing the 0 on imported records. I'd prefer a trigger to update the mobile field with 0 in front on every insert of the imported rows.

Thanks

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-12-01 : 14:16:52

quote:
Originally posted by hummy

thanks for your responses. But i don't think a one time update will do. Because we do this import every week having been provided the file from a third party. So every week the mobile field is missing the 0 on imported records. I'd prefer a trigger to update the mobile field with 0 in front on every insert of the imported rows.

Thanks

You should be aware that if you are using a bulk insert method, like BCP or BULK INSERT, to insert the data into the database it will not cause the trigger to fire.

If you are using SQL statements to insert the data into the table, you can use the formatting method suggested below for the UPDATE statement in your INSERT statement.

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources