SQLTeam.com | Weblogs | Forums

How to extract numbers from a field in a table?


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?




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


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


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

	Table1 AS t1
		STUFF(t1.field1,1,4,'') = t2.field2

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


Thanks Scott!