remove alfa values and make numeric

Question

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

Answer 1

0

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: RIGHT('0000000' + <a href='http://a.sn'>a.sn</a>, 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.

answered Mar 30 '12 at 06:29 PM

Pavel Pawlowski
20.3k ● 5 ● 10 ● 20

well thanks for suggestion. I did this way and it worked for me.

select fname, lname from #temp1 a inner join #temp2 b on

SUBSTRING(a.sn,PATINDEX('%[0-9]%',a.sn),LEN(a.sn))=CONVERT(VARCHAR,b.sn)

Mar 30 '12 at 08:03 PM binodbabu

could you ever have a value like a1b24?

Mar 30 '12 at 08:44 PM KenJ

oh at that time i'll use function???:p

Apr 18 '12 at 09:28 PM binodbabu

(comments are locked)

remove alfa values and make numeric

Follow this question

Related Questions