title: Intercapitalization in Standard Sql with exceptions
body: <p>I have list of strings which are to be inter-capitalized.</p>
  
  <p>So far I did</p>
  
  <hr>
  
  <pre><code>CREATE TEMP FUNCTION INTERCAPS(my_text STRING)
  
  AS (
  
  (
  
  SELECT
  
  STRING_AGG(
  
  CONCAT(UPPER(SUBSTR(list, 1, 1)), LOWER(SUBSTR(list, 2))),
  
  ' ' ORDER BY placement
  
  )
  
  FROM UNNEST(SPLIT(my_text, ' ')) as list WITH OFFSET placement
  
  )
  
  );
  
  WITH test_data AS (
  
  SELECT '8th avenue' AS my_text 
  
  UNION ALL
  
  SELECT '88 VALLEY Stream JiMMy Lee BRIDGE' AS my_text
  
  UNION ALL 
  
  SELECT "hen And HEN'S ROAD" AS my_text
  
  UNION ALL 
  
  SELECT 'Water of Leith BRIDGE' AS my_text
  
  UNION ALL 
  
  SELECT 'Anne McLean DRIVE' AS my_text
  
  UNION ALL 
  
  SELECT "John O'Groats ROAD" AS my_text
  
  UNION ALL 
  
  SELECT 'Bowlers Creek (MacKenzies) BRIDGE' AS my_text
  
  UNION ALL 
  
  SELECT 'Clutha RIVER (Luggate) BRIDGE - (mini park) Bridge' AS my_text
  
  UNION ALL 
  
  SELECT "Cross ROAD (O'Neill ROAD)" AS my_text
  
  )
  
  //
  
  SELECT my_text, INTERCAPS(my_text) as formatted
  
  FROM test_data
  
  GROUP BY my_text, formatted
  
  ;
  </code></pre>
  
  <hr>
  
  <p>Need help in formatting row 3 - row 9 data.</p>
  
  <p><a href="https://i.stack.imgur.com/rdLlO.png" rel="nofollow noreferrer">Current and Expected Output</a></p>
  
  <p><strong>my_text</strong></p>
  
  <p>8th avenue</p>
  
  <p>88 VALLEY Stream JiMMy Lee BRIDGE</p>
  
  <p>hen And HEN'S ROAD</p>
  
  <p>Water of Leith BRIDGE</p>
  
  <p>Anne McLean DRIVE</p>
  
  <p>John O'Groats ROAD</p>
  
  <p>Bowlers Creek (MacKenzies) BRIDGE</p>
  
  <p>Clutha RIVER (Luggate) BRIDGE - (mini park) Bridge</p>
  
  <p>Cross ROAD (O'Neill ROAD)</p>
  
  <p><strong>formatted</strong></p>
  
  <p>8th Avenue</p>
  
  <p>88 Valley Stream Jimmy Lee Bridge</p>
  
  <p>Hen And Hen's Road</p>
  
  <p>Water Of Leith Bridge</p>
  
  <p>Anne Mclean Drive</p>
  
  <p>John O'groats Road</p>
  
  <p>Bowlers Creek (mackenzies) Bridge</p>
  
  <p>Clutha River (luggate) Bridge - (mini Park) Bridge</p>
  
  <p>Cross Road (o'neill Road)</p>
  
  <p><strong>Expected Output</strong></p>
  
  <pre><code>8th Avenue
  
  88 Valley Stream Jimmy Lee Bridge
  
  Hen and Hen's Road
  
  Water of Leith Bridge
  
  Anne McLean Drive
  
  John O'Groats Road
  
  Bowlers Creek (MacKenzies) Bridge
  
  Clutha River (Luggate) Bridge - (Mini Park) Bridge
  
  Cross Road (O'Neill Road)
  </code></pre>
  
  <hr>
  
  <p>Basic rule: </p>
  
  <p>Starting alphabet &amp; each alphabet after "space" are to be made in uppercase. </p>
  
  <p>However don't want to format conjunction words like "and" / "of". In my data-set McLean &amp; Mclean are unique names so don't want to mix them. </p>
  
  <p>I was thinking of using regexp_replace using  </p>
  
  <blockquote>
    <p>r'\(.'</p>
  </blockquote>
  
  <p>to uppercase first letter after ( in formatted</p>
  
  <p>and   </p>
  
  <blockquote>
    <p>r'Mac.|Mc.'</p>
  </blockquote>
  
  <p>to uppercase the formatted <strong>only if</strong> regexp_match Mac[A-Z] in original text but couldn't get hang of it. </p>
  
  <hr>
  
  <p>select match from UNNEST(REGEXP_EXTRACT_ALL("xyz and McLean and MacKenzies", r'Mac.|Mc.')) as match WITH OFFSET AS position ORDER BY position; </p>
  
  <p>---- Hope this further helps. </p>
  
  <p>Many thanks in advance :) </p>