asked
remove alfa values and make numeric
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?? create table #temp1(sn varchar(5), fname varchar(5)) insert into #temp1 values ('123','abc') insert into #temp1 values ('ab124','def') insert into #temp1 values ('xz125','abg') create table #temp2(sn int, lname varchar) insert into #temp2 values(123, 'xyz') insert into #temp2 values(124, 'xyz') insert into #temp2 values(125, 'xyz') select fname, lname from #temp1 a 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][1] 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: **`RIGHT('0000000' +
a.sn, 3)`** to get number. But as mentioned, this assumes, that the numbers are always 3 digits and only prefixed. 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. [1]:
http://www.pawlowski.cz/2010/09/sql-server-2005-and-sql-server-2008-regular-expressions-and-pattern-matching-2/
4 People are following this question.
