I have a phone numbers table with two fields, one is the phone number as entered by the user, as in "(915) 771-7058", and the other is supposed to have the clean representation of the number to enforce uniqueness, as in "9157717058".

I've tried 2 approaches, one is filling the "clean" field in my VFP front end, the second is doing it with an update trigger. I think the former is the rigt way to go, except that I'm not sure if I'm doing it right, since I'm filling the clean value by doing an update inside the trigger, so every update turns into two updates, here's the code:

CREATE TRIGGER PLAINNO ON dbo.custTel 
FOR INSERT, UPDATE
AS

declare @nRows  integer 

select @nRows = @@rowcount

if @nRows > 0
	if update( cTelNo )	
	begin
		declare @cString varchar(20)
		declare @nLen integer 
		declare @i integer
		declare @cClean varchar(20)
		declare @cNext char
		declare @nTelID integer

		select @cString = cTelNo from inserted
		select @i = 1
		select @nLen = len( @cString )
		select @cClean = ''
		select @nTelID = nTelID from inserted

		while @i <= @nLen
			begin
			select @cNext = substring( @cString , @i , 1 )
			if (ascii( @cNext ) > 47) and (ascii( @cNext ) < 58)
				select @cClean = @cClean + @cNext
			select @i = @i + 1
			end

                /* Is this how it's done? */
		update custtel set cPlainNo = @cClean where nTelID = @nTelID

	end