question

binodbabu avatar image
binodbabu 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
sql-server-2008sql-server-2005tsql
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Pavel Pawlowski avatar image
Pavel Pawlowski answered
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/
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

binodbabu avatar image binodbabu commented ·
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)
0 Likes 0 ·
KenJ avatar image KenJ commented ·
could you ever have a value like `a1b24`?
0 Likes 0 ·
binodbabu avatar image binodbabu commented ·
oh at that time i'll use function???:p
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.