How to find similar string value in Bigquery

As I understand, there are some names in both tables that are not exactly the same and vary by some number of characters. For instance, there is a character d missing in Akhun(d)zada but present in AKHUNDZADA.

To find similarity between 2 strings, the Jaccard distance or the Levenshtein distance UDFs can be used. The jaccard() and levenshtein() functions are built-in UDFs (community provided) in BigQuery. Other UDFs available in BigQuery can be found here.

Consider the below query for your use case. jaccard() UDF has been used in this query.

SELECT distinct table_1.Name, table_2.Name as Name_table_2
FROM (
    SELECT Name, trim(name_unnest) as name_trim
    FROM 
    `project-id.dataset-id.table-1`, unnest(split(Name, "alias")) as name_unnest 
) as table_1, `project-id.dataset-id.table-2` as table_2 
where bqutil.fn.jaccard(lower(table_1.name_trim), lower(table_2.Name))>=0.8
order by Name;

Output of the query:

Also, please note that for the given set of inputs, a threshold of 0.8 works well. The threshold might not hold good for other inputs and has to be adjusted accordingly. The lower the threshold, the more distinct the names will become and lesser is the similarity.

You are suffering from a bad data model. The string in table1 can hold multiple values, thus violating database normalization.

To go about this, you can split the string into its separate values. In your case, however, you need two steps for this:

• Split the strings at the key word "alias"
• For each word containing additional information in parentheses, add a string without that information. (E.g. for 'Khatiba Imam Al-Bukhari (KIB)' you want to add 'Khatiba Imam Al-Bukhari' to your list of search strings.)

Once you have done this, you want to compare regardless of upper/lower case (so that 'AHMAD JAN AKHUNZADA SHUKOOR AKHUNDZADA' matches 'ahmad jan Akhunzada shukoor akhundzada').

In Google BigQuery you get from a list in a string to a proper array with SPLIT. Then, you can use UNNEST to get from the array to single rows with one name each. For removing text in parantheses, I'd use REGEXP_REPLACE. And for a case insensitive comparison you can convert all characters to lower case.

with raw_names as
(
  select
    names as names_string,
    trim(raw_name) as name
  from table1
  cross join unnest(lower(names), 'alias') as raw_name
)
, names as
(
  select
    names_string, trim(raw_name) as name
  from raw_names
  union
  select
    names_string, trim(regexp_replace(raw_name, '\([^\)]*\)', '')) as name
  from raw_names
)
select names.names_string, table2.name
from names
join table2 on lower(table2.name) = names.name
order by names.names_string, table2.name;

This is untested. I have never worked with BigQuery, so I hope that I got everything right.

Try the SQL joins to compare the two tables.

SELECT Table_1.Name FROM Table_1
LEFT JOIN Table_2 ON Table_1.Name = Table_2.Name;