Regex_replace in snowflake using pattern

Instead of using REGEXP_REPLACE, I suggest you write a UDF (JavaScript or Java), and use regexp of JavaScript (or java). It will be much cleaner and maintainable.

https://docs.snowflake.com/en/sql-reference/user-defined-functions.html

Here is a sample function:

CREATE OR REPLACE FUNCTION transform_text (STR VARCHAR)
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
AS $$
  var abbreviations = { "inc.": "incorporation", "&": "and" };

  // remove articles from the beginning
  var Result = STR.replace( /^(a|an|the) /i, "" );

  // remove the special characters
  Result = Result.replace( /(;|,|:|%)/g, "" );

  // convert abbreviations
  for (var abv in abbreviations) Result = Result.replace( abv, abbreviations[abv] );

  return (Result);
$$
;

select transform_text( 'A good, a:; bo%ok & hoyd inc.' ) as Result;


+------------------------------------+
|               RESULT               |
+------------------------------------+
| good a book and hoyd incorporation |
+------------------------------------+

A couple tweaks on the excellent answer from Gokhan.

1. Convert the abbreviations prior to removing special chars
2. Special chars easier to remove with the ^ not one of these approach
3. Using \b to trap the word for the articles

CREATE OR REPLACE FUNCTION transform_text_2 (STR VARCHAR)
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
AS $$
  var abbreviations = { "inc.": "incorporation", "&": "and" };

  // remove articles from the beginning
  var Result = STR.replace( /\b(an?|the)\b /i, "" );

  // convert abbreviations
  for (var abv in abbreviations) Result = Result.replace( abv, abbreviations[abv] );

  // remove the special characters
  Result = Result.replace( /[^A-Za-z0-9 ]/g, "" );


  return (Result);
$$
;