x

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
more ▼

asked Mar 30 '12 at 04:17 PM in Default

binodbabu gravatar image

binodbabu
290 7 10 12

(comments are locked)
10|1200 characters needed characters left

1 answer: sort newest

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/
more ▼

answered Mar 30 '12 at 06:29 PM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.2k 8 11 21

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)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1933
x1816
x264

asked: Mar 30 '12 at 04:17 PM

Seen: 848 times

Last Updated: Apr 19 '12 at 07:22 AM