title: Is there a way to measure string similarity in Google BigQuery
body: <p>I'm wondering if anyone knows of a way to measure string similarity in BigQuery. </p>
  
  <p>Seems like would be a neat function to have. </p>
  
  <p>My case is i need to compare the similarity of two urls as want to be fairly sure they refer to the same article.</p>
  
  <p>I can find <a href="https://gist.github.com/andrei-m/982927" rel="noreferrer">examples using javascript</a> so maybe a UDF is the way to go but i've not used UDF's at all (or javascript for that matter :) )</p>
  
  <p>Just wondering if there may be a way using existing regex functions or if anyone might be able to get me started with porting the javascript example into a UDF. </p>
  
  <p>Any help much appreciated, thanks</p>
  
  <p>EDIT: Adding some example code</p>
  
  <p>So if i have a UDF defined as:</p>
  
  <pre><code>// distance function
  
  function levenshteinDistance (row, emit) {
  
    //if (row.inputA.length &lt;= 0 ) {var myresult = row.inputB.length};
    if (typeof row.inputA === 'undefined') {var myresult = 1};
    if (typeof row.inputB === 'undefined') {var myresult = 1};
    //if (row.inputB.length &lt;= 0 ) {var myresult = row.inputA.length};
  
      var myresult = Math.min(
          levenshteinDistance(row.inputA.substr(1), row.inputB) + 1,
          levenshteinDistance(row.inputB.substr(1), row.inputA) + 1,
          levenshteinDistance(row.inputA.substr(1), row.inputB.substr(1)) + (row.inputA[0] !== row.inputB[0] ? 1 : 0)
      ) + 1;
  
    emit({outputA: myresult})
  
  }
  
  bigquery.defineFunction(
    'levenshteinDistance',                           // Name of the function exported to SQL
    ['inputA', 'inputB'],                    // Names of input columns
    [{'name': 'outputA', 'type': 'integer'}],  // Output schema
    levenshteinDistance                       // Reference to JavaScript UDF
  );
  
  // make a test function to test individual parts
  
  function test(row, emit) {
    if (row.inputA.length &lt;= 0) { var x = row.inputB.length} else { var x = row.inputA.length};
    emit({outputA: x});
  }
  
  bigquery.defineFunction(
    'test',                           // Name of the function exported to SQL
    ['inputA', 'inputB'],                    // Names of input columns
    [{'name': 'outputA', 'type': 'integer'}],  // Output schema
    test                       // Reference to JavaScript UDF
  );
  </code></pre>
  
  <p>Any i try test with a query such as:</p>
  
  <pre><code>SELECT outputA FROM (levenshteinDistance(SELECT "abc" AS inputA, "abd" AS inputB))
  </code></pre>
  
  <p>I get error:</p>
  
  <p>Error: TypeError: Cannot read property 'substr' of undefined at line 11, columns 38-39
  Error Location: User-defined function</p>
  
  <p>It seems like maybe row.inputA is not a string perhaps or for some reason string functions not able to work on it. Not sure if this is a type issue or something funny about what utils the UDF is able to use by default. </p>
  
  <p>Again any help much appreciated, thanks. </p>