How to extract numbers from a field in a table?

Transact-SQL
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
1 Like
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)
2 Likes
6

Thanks Scott!

7

Thanks!

8

hi

i know this topic is from a long ago

i tried a different method using tally table
checking each character if it is a letter .. replace it with blank

Scott's solution is fine if data is like 'ABY-1234567893'
my solution takes care of "All Letters" if data is like this 'ABY-12Q45678OP93W'

if it helps great
:slight_smile:
:slight_smile:

drop create data 
use tempdb 

go 


drop table data
go 


create table data
(
field1 varchar(100) null
)
go 

insert into data select 'ABY-123456gggggg7893'

select * from data 
go 

drop table dat2
go

create table dat2
(
field1 varchar(100) null
)
go 

insert into dat2 select '1234567893'

select * from dat2 
go
SQL using tally table .. for a different use case scenario 
;WITH tally 
     AS (SELECT TOP (100) N=Row_number() 
                              OVER ( 
                                ORDER BY @@spid) 
         FROM   sys.all_columns), 
     data2 
     AS (SELECT field1, 
                col 
         FROM   data 
                CROSS apply (SELECT (SELECT c + '' 
                                     FROM   (SELECT n, 
                                                    Substring(field1, n, 1) C 
                                             FROM   tally 
                                             WHERE  n <= Datalength(field1)) [1] 
                                     WHERE  c BETWEEN '0' AND '9' 
                                     ORDER  BY n 
                                     FOR xml path(''))) p (col) 
         WHERE  p.col IS NOT NULL) 
SELECT b.field1, 
       Cast(col AS INT) IntCol 
FROM   data2 a 
       JOIN dat2 b 
         ON a.col = b.field1