hi i have problem in joining table where table a has alfanumeric values and table b has numeric values . I want to remove alfa values from table a and join the table (dont want to use function). here is the example of table: could you please help me to find out the solution??
inner join #temp2 b on a.sn(remove alfa and convert numeric) = b.sn
You will have to use a function to extract only the numeric part.
One of the approach and probably best performing could be using CLR RegEx implementation.
You can take a look here: [SQL Server 2005 and SQL Server 2008 Regular Expressions and pattern matching]
On the other side, based on your example.. If you know, that the numbers are only prefixed by different strings of particular length and if the numbers are always 3 digits, you could use:
Other thing is how the tables are being populated. If for example tables are being populated from external sources by some kind of ETL, then the removal of non numeric characters can be done in the ETL layer and you will have clean data in tables to perform join.: http://www.pawlowski.cz/2010/09/sql-server-2005-and-sql-server-2008-regular-expressions-and-pattern-matching-2/