declare @dirty varchar( 6 )
select @dirty = '120-dd'
select convert( int , 
case when substring( @dirty , 1 , 1 ) LIKE '[0-9]' then substring( @dirty , 1 , 1 ) else '' end +
case when substring( @dirty , 2 , 1 ) LIKE '[0-9]' then substring( @dirty , 2 , 1 ) else '' end +
case when substring( @dirty , 3 , 1 ) LIKE '[0-9]' then substring( @dirty , 3 , 1 ) else '' end +
case when substring( @dirty , 4 , 1 ) LIKE '[0-9]' then substring( @dirty , 4 , 1 ) else '' end +
case when substring( @dirty , 5 , 1 ) LIKE '[0-9]' then substring( @dirty , 5 , 1 ) else '' end +
case when substring( @dirty , 6 , 1 ) LIKE '[0-9]' then substring( @dirty , 6 , 1 ) else '' end 
) AS NumericValueOnly
/*
NumericValueOnly 
---------------- 
             120 

*/

This page was last updated on May 01, 2006 04:28 PM.