SQLTeam.com | Weblogs | Forums

How to extract numbers from a field in a table?


#1

Hi, I have a field1 in my table1 that has letters and numbers: ABY-1234567893

i like to join that field on a field2 in my table 2. the issue is that field2 in my table 2 doesn't have letters: it's populated as numbers only :1234567893

my question is how to get rid of letters and dash in my field 1 so i can be able to join it on field 2 in my table 2 and would I be able to join it on field 2 after getting rid of letters?

Thanks,

Mike


#2

Sample data in the form of create table and inserts please.


#3

not sure how to post this here but i have 2 tables (table 1 and 2), both have a common field (field 1 and field 2)
fields 1 and 2 are the same except that field 1 has 3 letters and a dash i front. i want to join both tables on field 1 and 2


#4

You can join like shown below. It is likely to be inefficient because the query will have to scan the entire table.

FROM
	Table1 AS t1
	INNER JOIN Table2 AS t2 ON
		STUFF(t1.field1,1,4,'') = t2.field2

#5
SELECT t1.*, t2.*
FROM dbo.table1 t1
INNER JOIN dbo.table2 t2 ON 
    t2.field2 = SUBSTRING(t1.field1, PATINDEX('%[0-9]%', t1.field1), 100)

#6

Thanks Scott!


#7

Thanks!