replace_en_contractions - BigQuery

SELECT `justfunctions.eu.replace_en_contractions`("I'll be great tmr thx", "r"\s+"")
                                            
/*--Output--
I will be great tomorrow thanks
*/

CREATE OR REPLACE FUNCTION `your_project_id.your_dataset_id.replace_en_contractions`(`string` string, `replacement` string) 
  RETURNS string AS ((WITH 
words AS (SELECT * FROM unnest(SPLIT(REGEXP_REPLACE(LOWER(string),replacement," "), " ")) as word),

list AS (SELECT LOWER(JSON_EXTRACT_SCALAR(c,"$.key")) key_part,LOWER(JSON_EXTRACT_SCALAR(c,"$.value")) value_part  FROM
unnest([
'{"key":"I\'m","value":"I am"}',
'{"key":"I\'m\'a","value":"I am about to"}',
'{"key":"I\'m\'o","value":"I am going to"}',
'{"key":"I\'ve","value":"I have"}',
'{"key":"I\'ll","value":"I will"}',
'{"key":"I\'ll\'ve","value":"I will have"}',
'{"key":"I\'d","value":"I would"}',
'{"key":"I\'d\'ve","value":"I would have"}',
'{"key":"Whatcha","value":"What are you"}',
'{"key":"amn\'t","value":"am not"}',
'{"key":"ain\'t","value":"are not"}',
'{"key":"aren\'t","value":"are not"}',
'{"key":"\'cause","value":"because"}',
'{"key":"can\'t","value":"cannot"}',
'{"key":"can\'t\'ve","value":"cannot have"}',
'{"key":"could\'ve","value":"could have"}',
'{"key":"couldn\'t","value":"could not"}',
'{"key":"couldn\'t\'ve","value":"could not have"}',
'{"key":"daren\'t","value":"dare not"}',
'{"key":"daresn\'t","value":"dare not"}',
'{"key":"dasn\'t","value":"dare not"}',
'{"key":"didn\'t","value":"did not"}',
'{"key":"didn’t","value":"did not"}',
'{"key":"don\'t","value":"do not"}',
'{"key":"don’t","value":"do not"}',
'{"key":"doesn\'t","value":"does not"}',
'{"key":"e\'er","value":"ever"}',
'{"key":"everyone\'s","value":"everyone is"}',
'{"key":"finna","value":"fixing to"}',
'{"key":"gimme","value":"give me"}',
'{"key":"gon\'t","value":"go not"}',
'{"key":"gonna","value":"going to"}',
'{"key":"gotta","value":"got to"}',
'{"key":"hadn\'t","value":"had not"}',
'{"key":"hadn\'t\'ve","value":"had not have"}',
'{"key":"hasn\'t","value":"has not"}',
'{"key":"haven\'t","value":"have not"}',
'{"key":"he\'ve","value":"he have"}',
'{"key":"he\'s","value":"he is"}',
'{"key":"he\'ll","value":"he will"}',
'{"key":"he\'ll\'ve","value":"he will have"}',
'{"key":"he\'d","value":"he would"}',
'{"key":"he\'d\'ve","value":"he would have"}',
'{"key":"here\'s","value":"here is"}',
'{"key":"how\'re","value":"how are"}',
'{"key":"how\'d","value":"how did"}',
'{"key":"how\'d\'y","value":"how do you"}',
'{"key":"how\'s","value":"how is"}',
'{"key":"how\'ll","value":"how will"}',
'{"key":"isn\'t","value":"is not"}',
'{"key":"it\'s","value":"it is"}',
'{"key":"\'tis","value":"it is"}',
'{"key":"\'twas","value":"it was"}',
'{"key":"it\'ll","value":"it will"}',
'{"key":"it\'ll\'ve","value":"it will have"}',
'{"key":"it\'d","value":"it would"}',
'{"key":"it\'d\'ve","value":"it would have"}',
'{"key":"kinda","value":"kind of"}',
'{"key":"let\'s","value":"let us"}',
'{"key":"luv","value":"love"}',
'{"key":"ma\'am","value":"madam"}',
'{"key":"may\'ve","value":"may have"}',
'{"key":"mayn\'t","value":"may not"}',
'{"key":"might\'ve","value":"might have"}',
'{"key":"mightn\'t","value":"might not"}',
'{"key":"mightn\'t\'ve","value":"might not have"}',
'{"key":"must\'ve","value":"must have"}',
'{"key":"mustn\'t","value":"must not"}',
'{"key":"mustn\'t\'ve","value":"must not have"}',
'{"key":"needn\'t","value":"need not"}',
'{"key":"needn\'t\'ve","value":"need not have"}',
'{"key":"ne\'er","value":"never"}',
'{"key":"o\'","value":"of"}',
'{"key":"o\'clock","value":"of the clock"}',
'{"key":"ol\'","value":"old"}',
'{"key":"oughtn\'t","value":"ought not"}',
'{"key":"oughtn\'t\'ve","value":"ought not have"}',
'{"key":"o\'er","value":"over"}',
'{"key":"shan\'t","value":"shall not"}',
'{"key":"sha\'n\'t","value":"shall not"}',
'{"key":"shalln\'t","value":"shall not"}',
'{"key":"shan\'t\'ve","value":"shall not have"}',
'{"key":"she\'s","value":"she is"}',
'{"key":"she\'ll","value":"she will"}',
'{"key":"she\'d","value":"she would"}',
'{"key":"she\'d\'ve","value":"she would have"}',
'{"key":"should\'ve","value":"should have"}',
'{"key":"shouldn\'t","value":"should not"}',
'{"key":"shouldn\'t\'ve","value":"should not have"}',
'{"key":"so\'ve","value":"so have"}',
'{"key":"so\'s","value":"so is"}',
'{"key":"somebody\'s","value":"somebody is"}',
'{"key":"someone\'s","value":"someone is"}',
'{"key":"something\'s","value":"something is"}',
'{"key":"sux","value":"sucks"}',
'{"key":"that\'re","value":"that are"}',
'{"key":"that\'s","value":"that is"}',
'{"key":"that\'ll","value":"that will"}',
'{"key":"that\'d","value":"that would"}',
'{"key":"that\'d\'ve","value":"that would have"}',
'{"key":"\'em","value":" them"}',
'{"key":"there\'re","value":"there are"}',
'{"key":"there\'s","value":"there is"}',
'{"key":"there\'ll","value":"there will"}',
'{"key":"there\'d","value":"there would"}',
'{"key":"there\'d\'ve","value":"there would have"}',
'{"key":"these\'re","value":"these are"}',
'{"key":"they\'re","value":"they are"}',
'{"key":"they\'ve","value":"they have"}',
'{"key":"they\'ll","value":"they will"}',
'{"key":"they\'ll\'ve","value":"they will have"}',
'{"key":"they\'d","value":"they would"}',
'{"key":"they\'d\'ve","value":"they would have"}',
'{"key":"this\'s","value":"this is"}',
'{"key":"this\'ll","value":"this will"}',
'{"key":"this\'d","value":"this would"}',
'{"key":"those\'re","value":"those are"}',
'{"key":"to\'ve","value":"to have"}',
'{"key":"wanna","value":"want to"}',
'{"key":"wasn\'t","value":"was not"}',
'{"key":"we\'re","value":"we are"}',
'{"key":"we\'ve","value":"we have"}',
'{"key":"we\'ll","value":"we will"}',
'{"key":"we\'ll\'ve","value":"we will have"}',
'{"key":"we\'d","value":"we would"}',
'{"key":"we\'d\'ve","value":"we would have"}',
'{"key":"weren\'t","value":"were not"}',
'{"key":"what\'re","value":"what are"}',
'{"key":"what\'d","value":"what did"}',
'{"key":"what\'ve","value":"what have"}',
'{"key":"what\'s","value":"what is"}',
'{"key":"what\'ll","value":"what will"}',
'{"key":"what\'ll\'ve","value":"what will have"}',
'{"key":"when\'ve","value":"when have"}',
'{"key":"when\'s","value":"when is"}',
'{"key":"where\'re","value":"where are"}',
'{"key":"where\'d","value":"where did"}',
'{"key":"where\'ve","value":"where have"}',
'{"key":"where\'s","value":"where is"}',
'{"key":"which\'s","value":"which is"}',
'{"key":"who\'re","value":"who are"}',
'{"key":"who\'ve","value":"who have"}',
'{"key":"who\'s","value":"who is"}',
'{"key":"who\'ll","value":"who will"}',
'{"key":"who\'ll\'ve","value":"who will have"}',
'{"key":"who\'d","value":"who would"}',
'{"key":"who\'d\'ve","value":"who would have"}',
'{"key":"why\'re","value":"why are"}',
'{"key":"why\'d","value":"why did"}',
'{"key":"why\'ve","value":"why have"}',
'{"key":"why\'s","value":"why is"}',
'{"key":"will\'ve","value":"will have"}',
'{"key":"won\'t","value":"will not"}',
'{"key":"won\'t\'ve","value":"will not have"}',
'{"key":"would\'ve","value":"would have"}',
'{"key":"wouldn\'t","value":"would not"}',
'{"key":"wouldn\'t\'ve","value":"would not have"}',
'{"key":"y\'all","value":"you all"}',
'{"key":"y\'all\'re","value":"you all are"}',
'{"key":"y\'all\'ve","value":"you all have"}',
'{"key":"y\'all\'d","value":"you all would"}',
'{"key":"y\'all\'d\'ve","value":"you all would have"}',
'{"key":"you\'re","value":"you are"}',
'{"key":"you\'ve","value":"you have"}',
'{"key":"you\'ll\'ve","value":"you shall have"}',
'{"key":"you\'ll","value":"you will"}',
'{"key":"you\'d","value":"you would"}',
'{"key":"you\'d\'ve","value":"you would have"}',
'{"key":"\'aight","value":"alright"}',
'{"key":"abt","value":"about"}',
'{"key":"acct","value":"account"}',
'{"key":"altho","value":"although"}',
'{"key":"asap","value":"as soon as possible"}',
'{"key":"avg","value":"average"}',
'{"key":"b4","value":"before"}',
'{"key":"bc","value":"because"}',
'{"key":"bday","value":"birthday"}',
'{"key":"btw","value":"by the way"}',
'{"key":"convo","value":"conversation"}',
'{"key":"cya","value":"see ya"}',
'{"key":"diff","value":"different"}',
'{"key":"dunno","value":"do not know"}',
'{"key":"g\'day","value":"good day"}',
'{"key":"howdy","value":"how do you do"}',
'{"key":"idk","value":"I do not know"}',
'{"key":"ima","value":"I am going to"}',
'{"key":"imma","value":"I am going to"}',
'{"key":"innit","value":"is it not"}',
'{"key":"iunno","value":"I do not know"}',
'{"key":"kk","value":"okay"}',
'{"key":"lemme","value":"let me"}',
'{"key":"msg","value":"message"}',
'{"key":"nvm","value":"nevermind"}',
'{"key":"ofc","value":"of course"}',
'{"key":"ppl","value":"people"}',
'{"key":"prolly","value":"probably"}',
'{"key":"pymnt","value":"payment"}',
'{"key":"r ","value":"are "}',
'{"key":"rlly","value":"really"}',
'{"key":"rly","value":"really"}',
'{"key":"rn","value":"right now"}',
'{"key":"spk","value":"spoke"}',
'{"key":"tbh","value":"to be honest"}',
'{"key":"tho","value":"though"}',
'{"key":"thx","value":"thanks"}',
'{"key":"tlked","value":"talked"}',
'{"key":"tmmw","value":"tomorrow"}',
'{"key":"tmr","value":"tomorrow"}',
'{"key":"tmrw","value":"tomorrow"}',
'{"key":"u","value":"you"}',
'{"key":"ur","value":"you are"}',
'{"key":"woulda","value":"would have"}',
'{"key":"\'all","value":""}',
'{"key":"\'am","value":""}',
'{"key":"\'d","value":" would"}',
'{"key":"\'ll","value":" will"}',
'{"key":"\'re","value":" are"}',
'{"key":"doin\'","value":"doing"}',
'{"key":"goin\'","value":"going"}',
'{"key":"nothin\'","value":"nothing"}',
'{"key":"somethin\'","value":"something"}',
'{"key":"havin\'","value":"having"}',
'{"key":"lovin\'","value":"loving"}',
'{"key":"\'coz","value":"because"}',
'{"key":"thats","value":"that is"}',
'{"key":"whats","value":"what is"}']) as c)
SELECT string_agg(CASE WHEN value_part IS NOT NULL THEN value_part ELSE word END," ") final  FROM words LEFT JOIN list ON list.key_part=words.word
)
)
  OPTIONS ( description = '''Replaces English contractions in a <string> with the specified <replacement>.''')

SQL User Defined Function (SQL UDF)