SQL Server Trigger to strip non numeric from string and insert into int column

rtay
rtay used Ask the Experts™
on
I am trying to strip all non numeric characters from a field in a sql table and insert it back into another field in the same table.  The original field is a string field and the destination is an INT field.  Below is the basic transfer trigger, but will not work because of string to int.  I need the sql code to remove the non numeric characters before the insert.  

Thanks in advance.

CReate TRIGGER [dbo].[trg_update_Trailerchange2] on [dbo].[tbl_TrailerInfoTHisONe] FOR UPDATE AS            
BEGIN
    UPDATE tbl_TrailerInfoTHisONe
    SET SemiTrlCopy = SemiTrlNo
    FROM tbl_TrailerInfoTHisONe INNER JOIN deleted d
    on tbl_TrailerInfoTHisONe.TrailerID = d.TrailerID
END


GO
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2012
Commented:
You can use a function like this:
CREATE FUNCTION dbo.udf_GetNumericOnly (@Value varchar(50))

RETURNS varchar(50)  

AS  

BEGIN 

Declare @Pos tinyint,
	@Char char(1)

Set @Value = REPLACE(@Value, ' ', '')		-- Strip all spaces
Set @Pos = LEN(@Value)			-- Give some non-zero value
While @Pos > 0
    Begin
	Set @Pos = PATINDEX('%[^0-9]%', @Value)		-- This will remove all the non-numeric characters (the spaces have alaready been removed with the REPLACE)
	If @Pos > 0 
	   Begin
		Set @Char = SUBSTRING(@Value, @Pos, 1)		-- Non numeric character
		Set @Value = REPLACE(@Value, @Char, '')
	   End
    End

Return @Value

END

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial