SQL pattern recognition, exclude last few characters-CodePudding

I have two tables with columns a, b

| t1.column a          | t2.Column b  
| ---------------------| --------- |
| varchar [00000000]   |00000002-03| 
| varchar [00000001]   |00000000-01| 
| varchar [00000002]   |00000001-01|

I want to collect all values from column a that match values in column b ignoring the last three characters '-00'.

My result should look like

|       a              |     b  
| ---------------------| --------- |
| varchar [00000000]   |00000000-01| 
| varchar [00000001]   |00000001-01| 
| varchar [00000002]   |00000002-03|

My query

select
 t1.column_a
,t2.column_b
,etc
from t1,t2
where column_a like '%column_b%'

I get nothing because of the formatting of column b.

what I'd ideally like to do is match column a to ('%column b%' Minus those last three characters.) Any ideas? Thanks!

CodePudding user response：

One method uses split_part():

select . . .
from t1 join
     t2
     on t1.column_a = split_part(t2.column_b, '-', 1)

CodePudding user response：

SELECT SUBSTRING('SQL Tutorial', 1, 3) FROM table;

result: SQL

so perhaps

select
t1.column_a
t2.column_b
from t1,t2
where column_a IN (select SUBSTRING (t2.column_b,1,8) from t1,t2);