API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Apr 23rd, 2019
93
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 11.86 KB | None | 0 0
raw download clone embed print report
  1. ,CASE WHEN REGEXP_INSTR(TEXT, '(\d+|\b\w*ième|plusieurs*)\sfois(?:\W+\w+){1,5}?\W+r{0,1}(appel|conta|[éeè]cri|demand), 1,1,1,'i') > 0 THEN 1 ELSE 0 END AS irrit_reiteration_0
  2. ,CASE WHEN REGEXP_INSTR(TEXT, '\s(appel[a-zéèer]*|t[eéè]l[eéè]phon[éerè]+)(\W+\w+){,5}\sfois, 1,1,1,'i') > 0 THEN 1 ELSE 0 END AS irrit_reiteration_1
  3. ,CASE WHEN REGEXP_INSTR(TEXT, 'à chaque fois, 1,1,1,'i') > 0 THEN 1 ELSE 0 END AS irrit_reiteration_2
  4. ,CASE WHEN REGEXP_INSTR(TEXT, 'l[aes]+ dernières? fois, 1,1,1,'i') > 0 THEN 1 ELSE 0 END AS irrit_reiteration_3
  5. ,CASE WHEN REGEXP_INSTR(TEXT, '[çcels]+a\sfai[ts]*(\W+\w+){,5}\sfoi, 1,1,1,'i') > 0 THEN 1 ELSE 0 END AS irrit_reiteration_4
  6. ,CASE WHEN REGEXP_INSTR(TEXT, 'pas\sla\spremi[eéè]re\sfois, 1,1,1,'i') > 0 THEN 1 ELSE 0 END AS irrit_reiteration_5
  7. ,CASE WHEN REGEXP_INSTR(TEXT, 'plusieurs*\sfoi[se]\sauparavant, 1,1,1,'i') > 0 THEN 1 ELSE 0 END AS irrit_reiteration_6
  8. ,CASE WHEN REGEXP_INSTR(TEXT, 'atten[dres]+(\W+\w+){,5}\srembours, 1,1,1,'i') > 0 THEN 1 ELSE 0 END AS irrit_remboursement_tardif_0
  9. ,CASE WHEN REGEXP_INSTR(TEXT, 'pas(\W+\w+){,5}\s(re[cç]us?|eu)(\W+\w+){,5}\srembours, 1,1,1,'i') > 0 THEN 1 ELSE 0 END AS irrit_remboursement_tardif_1
  10. ,CASE WHEN REGEXP_INSTR(TEXT, 'a[vezr]*\s(rien|pas)\sr[eéè]pondu, 1,1,1,'i') > 0 THEN 1 ELSE 0 END AS irrit_absence_reponse_0
  11. ,CASE WHEN REGEXP_INSTR(TEXT, 'répond[erzs]*\s(\W+\w+){,5}(pas|jamais)(\W+\w+){,5}\s(questions?), 1,1,1,'i') > 0 THEN 1 ELSE 0 END AS irrit_absence_reponse_1
  12. ,CASE WHEN REGEXP_INSTR(TEXT, 'pas(\W+\w+){1,5}\sr[eèé]pon[erzs]*(\W+\w+){1,5}\squestions?, 1,1,1,'i') > 0 THEN 1 ELSE 0 END AS irrit_absence_reponse_2
  13. ,CASE WHEN REGEXP_INSTR(TEXT, 'pas(\W+\w+){1,5}\sr[éèe]pon[deurzs]*, 1,1,1,'i') > 0 THEN 1 ELSE 0 END AS irrit_absence_reponse_3
  14. ,CASE WHEN REGEXP_INSTR(TEXT, 'refus+(\W+\w+){1,5}\sr[éeè]pon[deurzs]*, 1,1,1,'i') > 0 THEN 1 ELSE 0 END AS irrit_absence_reponse_4
  15. ,CASE WHEN REGEXP_INSTR(TEXT, 'aucun[nes]*\sr[eéè]ponse\sde\svo, 1,1,1,'i') > 0 THEN 1 ELSE 0 END AS irrit_absence_reponse_5
  16. ,CASE WHEN REGEXP_INSTR(TEXT, 'je\s(reste|suis)\ssans\sr[éeè]ponse, 1,1,1,'i') > 0 THEN 1 ELSE 0 END AS irrit_absence_reponse_6
  17. ,CASE WHEN REGEXP_INSTR(TEXT, '(aucuns*|sans|pas\sde|pas\seu\sde)\sretour, 1,1,1,'i') > 0 THEN 1 ELSE 0 END AS irrit_absence_reponse_7
  18. ,CASE WHEN REGEXP_INSTR(TEXT, '(ON|personne)(\s|\s(ne\s(me|m.a)\s)|\sne\s)r[éeè]pond, 1,1,1,'i') > 0 THEN 1 ELSE 0 END AS irrit_absence_reponse_8
  19. ,CASE WHEN REGEXP_INSTR(TEXT, '(arriv.{1,5}pa|impossible|probl[eéè]m).{0,15}(conne[cterxions]+|t[éèe]l[éèe]charg|espac.{1,5}clien), 1,1,1,'i') > 0 THEN 1 ELSE 0 END AS irrit_difficulte_acces_0
  20. ,CASE WHEN REGEXP_INSTR(TEXT, '(probl[eéè]).{1,15}(espac.{1,5}clien|identif|mot.{1,8}pass), 1,1,1,'i') > 0 THEN 1 ELSE 0 END AS irrit_difficulte_acces_1
  21. ,CASE WHEN REGEXP_INSTR(TEXT, '(espac.{1,5}clien|identif|mot.{1,8}pass).{1,10}march.{1,5}pa, 1,1,1,'i') > 0 THEN 1 ELSE 0 END AS irrit_difficulte_acces_2
  22. ,CASE WHEN REGEXP_INSTR(TEXT, 'la\slenteur\sde, 1,1,1,'i') > 0 THEN 1 ELSE 0 END AS irrit_lenteur_traitement_0
  23. ,CASE WHEN REGEXP_INSTR(TEXT, '[çsc]a\s(tra[iî]ne), 1,1,1,'i') > 0 THEN 1 ELSE 0 END AS irrit_lenteur_traitement_1
  24. ,CASE WHEN REGEXP_INSTR(TEXT, '(trop|si|très)\slong, 1,1,1,'i') > 0 THEN 1 ELSE 0 END AS irrit_lenteur_traitement_2
  25. ,CASE WHEN REGEXP_INSTR(TEXT, '(trop|autant)\sde\stemps, 1,1,1,'i') > 0 THEN 1 ELSE 0 END AS irrit_lenteur_traitement_3
  26. ,CASE WHEN REGEXP_INSTR(TEXT, '(erreur|contest|r[eéè]clam|pas\sles?\sbons?|incohé|souci).{1,20}(INDEX|rel[eéè]v[eéè]), 1,1,1,'i') > 0 THEN 1 ELSE 0 END AS irrit_contest_index_0
  27. ,CASE WHEN REGEXP_INSTR(TEXT, '(index|rel[eéè]v[eéè]).{1,30}(erron|faux|incoh[eéè]ren|corresponds?\spas), 1,1,1,'i') > 0 THEN 1 ELSE 0 END AS irrit_contest_index_1
  28. ,CASE WHEN REGEXP_INSTR(TEXT, '([cçs]a\s(me\s)?parr?a[îi].?|c.?\s?(est)?|\strouve\s[cçs]a)\s(vraiment\s)?[eèé]norme, 1,1,1,'i') > 0 THEN 1 ELSE 0 END AS irrit_contest_fact_0
  29. ,CASE WHEN REGEXP_INSTR(TEXT, 'comprends.{1,5}pas.{1,50}fact, 1,1,1,'i') > 0 THEN 1 ELSE 0 END AS irrit_contest_fact_1
  30. ,CASE WHEN REGEXP_INSTR(TEXT, '(comment.{1,10}|pas).{1,5}possible.{1,30}fact, 1,1,1,'i') > 0 THEN 1 ELSE 0 END AS irrit_contest_fact_2
  31. ,CASE WHEN REGEXP_INSTR(TEXT, 'factur((\S+)\s){1,4}(comment.{1,10}|pas).{1,4}possible, 1,1,1,'i') > 0 THEN 1 ELSE 0 END AS irrit_contest_fact_3
  32. ,CASE WHEN REGEXP_INSTR(TEXT, 'fact.{1,25}(\d\sfois(\W+\w+){1,5}?\s[eéè]lev|doublé|tripl), 1,1,1,'i') > 0 THEN 1 ELSE 0 END AS irrit_contest_fact_4
  33. ,CASE WHEN REGEXP_INSTR(TEXT, '([eéè]norme|trop).{1,40}(euros), 1,1,1,'i') > 0 THEN 1 ELSE 0 END AS irrit_contest_fact_5
  34. ,CASE WHEN REGEXP_INSTR(TEXT, '\squoi.((\S+)\s){0,4}fact, 1,1,1,'i') > 0 THEN 1 ELSE 0 END AS irrit_contest_fact_6
  35. ,CASE WHEN REGEXP_INSTR(TEXT, 'pourquoi.((\S+)\s){0,3}fact, 1,1,1,'i') > 0 THEN 1 ELSE 0 END AS irrit_contest_fact_7
  36. ,CASE WHEN REGEXP_INSTR(TEXT, '(r[eéè]clamation|contest(er|ation)).((\S+)\s){0,4}fact, 1,1,1,'i') > 0 THEN 1 ELSE 0 END AS irrit_contest_fact_8
  37. ,CASE WHEN REGEXP_INSTR(TEXT, 'fact.((\S+)\s){0,6}(r[eéè]clamation|contest(er|ation)), 1,1,1,'i') > 0 THEN 1 ELSE 0 END AS irrit_contest_fact_9
  38. ,CASE WHEN REGEXP_INSTR(TEXT, '(ne\sveu[xt]\s(pas|plus)|refus|changer|arr[eéè]t).{1,20}(pr[eéè]l[eéè]vement\sautomat|carte|ch[éeè]q|mode.{1,10}(paie|pr[eéè]lev)), 1,1,1,'i') > 0 THEN 1 ELSE 0 END AS irrit_process_fact_0
  39. ,CASE WHEN REGEXP_INSTR(TEXT, '(pr[eéè]l[eéè]vement\sautomat|carte|ch[éeè]q|(mode.{1,10}(paie|pr[eéè]lev))|mensualisation).{1,10}(ne\sme\sconvients?\s(pas|plus)), 1,1,1,'i') > 0 THEN 1 ELSE 0 END AS irrit_process_fact_1
  40. ,CASE WHEN REGEXP_INSTR(TEXT, 'd[eéè]j[aà]\s(pay|envo).{1,10}(fact|cheq), 1,1,1,'i') > 0 THEN 1 ELSE 0 END AS irrit_process_fact_2
  41. ,CASE WHEN REGEXP_INSTR(TEXT, 'ch[eéè]que.{1,5}[eéè]nergie\s((\S+)\s){1,8}pas\s((\S+)\s){0,2}(d[eéè]dui|d[eéè]bit), 1,1,1,'i') > 0 THEN 1 ELSE 0 END AS irrit_cheq_ener_0
  42. ,CASE WHEN REGEXP_INSTR(TEXT, '(transmi|transf[eéè]|remont|contact|appel|ALL|deman|vo[yi]|pass|v[eéè]rifi)((\S+)\s){0,3}service.{1,6}recouvrement, 1,1,1,'i') > 0 THEN 1 ELSE 0 END AS irrit_redirect_serv_recouvr_0
  43. ,CASE WHEN REGEXP_INSTR(TEXT, '(plei|toujour|beaucou|tout\sle\stem)((\S+)\s){1,8}(relanc|mails?\s?)((\S+)\s){1,8}servic((\S+)\s){1,3}recouvr, 1,1,1,'i') > 0 THEN 1 ELSE 0 END AS irrit_serv_recouvr_0
  44. ,CASE WHEN REGEXP_INSTR(TEXT, 'd[eé]j[aà]\s(appel|conta|vu\s)((\S+)\s){1,8}recouvr, 1,1,1,'i') > 0 THEN 1 ELSE 0 END AS irrit_serv_recouvr_1
  45. ,CASE WHEN REGEXP_INSTR(TEXT, '(probl[éeè]|essay.{1,10}faire|arrive.{1,5}pas|souci).{1,30}auto[-|\s]rel[eèé]ve, 1,1,1,'i') > 0 THEN 1 ELSE 0 END AS irrit_diff_auto_rlv_0
  46. ,CASE WHEN REGEXP_INSTR(TEXT, 'final.{1,10}signatur.{1,10}contr, 1,1,1,'i') > 0 THEN 1 ELSE 0 END AS irrit_process_souscription_0
  47. ,CASE WHEN REGEXP_INSTR(TEXT, '(av.{1,5}pa|aucune).{1,10}demand.{1,10}souscript, 1,1,1,'i') > 0 THEN 1 ELSE 0 END AS irrit_process_souscription_1
  48. ,CASE WHEN REGEXP_INSTR(TEXT, 'refaire\s((\S+)\s){1,5}souscript, 1,1,1,'i') > 0 THEN 1 ELSE 0 END AS irrit_process_souscription_2
  49. ,CASE WHEN REGEXP_INSTR(TEXT, '(pas.{1,10}(re[çc]u|activ)).{1,10}compteur, 1,1,1,'i') > 0 THEN 1 ELSE 0 END AS irrit_process_mise_en_serv_0
  50. ,CASE WHEN REGEXP_INSTR(TEXT, 'compteur.{1,20}(pas.{1,10}(activ|arriv)), 1,1,1,'i') > 0 THEN 1 ELSE 0 END AS irrit_process_mise_en_serv_1
  51. ,CASE WHEN REGEXP_INSTR(TEXT, 'ai(\W+\w+){,5}\s(rien|pas)\sre[cç]u.{1,10}fact, 1,1,1,'i') > 0 THEN 1 ELSE 0 END AS irrit_absence_recept_fact_0
  52. ,CASE WHEN REGEXP_INSTR(TEXT, '(j.\s?a|je\sn.\s?a|factur|nou|mar|conjoin|fil|femm)((\S+)\s){1,4}(pas|jamais|toujours\spas)\s[eéè]t[eéè].{1,5}(d[eéè]bit|pr[eéè]lev), 1,1,1,'i') > 0 THEN 1 ELSE 0 END AS irrit_absence_prel_fact_0
  53. ,CASE WHEN REGEXP_INSTR(TEXT, '(nous|on|vous)\s((\S+)\s){1,4}(pas|jamais|toujours\spas)\s[eéè]t[eéè].{1,5}(d[eéè]bit|pr[eéè]lev), 1,1,1,'i') > 0 THEN 1 ELSE 0 END AS irrit_absence_prel_fact_1
  54. ,CASE WHEN REGEXP_INSTR(TEXT, '(dysfonct|probl[eéè]|souci|anomali)((\S+)\s){1,3}compteu, 1,1,1,'i') > 0 THEN 1 ELSE 0 END AS irrit_dysfonct_compt_0
  55. ,CASE WHEN REGEXP_INSTR(TEXT, 'compteu.{1,20}(marche\s((\S+)\s){0,3}pas|souci|probl[eéè]), 1,1,1,'i') > 0 THEN 1 ELSE 0 END AS irrit_dysfonct_compt_1
  56. ,CASE WHEN REGEXP_INSTR(TEXT, 'd[eé]j[aà]\sfai((\S+)\s){1,5}r[eéè]siliat, 1,1,1,'i') > 0 THEN 1 ELSE 0 END AS irrit_resil_non_traite_0
  57. ,CASE WHEN REGEXP_INSTR(TEXT, 'd[ée]j[aà]\sdemand((\S+)\s){1,3}r[eéè]siliat, 1,1,1,'i') > 0 THEN 1 ELSE 0 END AS irrit_resil_non_traite_1
  58. ,CASE WHEN REGEXP_INSTR(TEXT, '(erreu|pas\snorma)((\S+)\s){1,8}contra((\S+)\s){1,5}actif, 1,1,1,'i') > 0 THEN 1 ELSE 0 END AS irrit_resil_non_traite_2
  59. ,CASE WHEN REGEXP_INSTR(TEXT, 'contra((\S+)\s){1,5}acti((\S+)\s){1,7}r[eéè]sil, 1,1,1,'i') > 0 THEN 1 ELSE 0 END AS irrit_resil_non_traite_3
  60. ,CASE WHEN REGEXP_INSTR(TEXT, 'r[eéè]sil((\S+)\s){1,7}contra((\S+)\s){1,3}toujours\sactif, 1,1,1,'i') > 0 THEN 1 ELSE 0 END AS irrit_resil_non_traite_4
  61. ,CASE WHEN REGEXP_INSTR(TEXT, 'jamai((\S+)\s){1,5}(demand)((\S+)\s){1,4}r[eéè]sil, 1,1,1,'i') > 0 THEN 1 ELSE 0 END AS irrit_resil_non_soll_0
  62. ,CASE WHEN REGEXP_INSTR(TEXT, '(une|la)\s(r[eéè]sil[eéèation]*).{1,5}tor[td], 1,1,1,'i') > 0 THEN 1 ELSE 0 END AS irrit_resil_non_soll_1
  63. ,CASE WHEN REGEXP_INSTR(TEXT, 'r[eéè]sil((\S+)\s){1,5}(pas|jamais).{1,8}demand, 1,1,1,'i') > 0 THEN 1 ELSE 0 END AS irrit_resil_non_soll_2
  64. ,CASE WHEN REGEXP_INSTR(TEXT, 'technicien.{0,10}\s(mais|jamais\svenu)\s, 1,1,1,'i') > 0 THEN 1 ELSE 0 END AS irrit_process_interv_0
  65. ,CASE WHEN REGEXP_INSTR(TEXT, 'intervention.{0,30}(déla|urge), 1,1,1,'i') > 0 THEN 1 ELSE 0 END AS irrit_process_interv_1
  66. ,CASE WHEN REGEXP_INSTR(TEXT, '(intervention|technicien).{1,30}(mal.{1,5}pass|toujour.{1,10}(pa|probl[eéè]m)), 1,1,1,'i') > 0 THEN 1 ELSE 0 END AS irrit_process_interv_2
  67. ,CASE WHEN REGEXP_INSTR(TEXT, 'technicien.{1,10}(aurait.{1,5}d[ûu]|devait?|pas).{1,5}ven, 1,1,1,'i') > 0 THEN 1 ELSE 0 END AS irrit_process_interv_3
  68.  
  69.  
  70. ,irrit_reiteration_0+irrit_reiteration_1+irrit_reiteration_2+irrit_reiteration_3+irrit_reiteration_4+irrit_reiteration_5+irrit_reiteration_6 AS irrit_reiteration
  71. ,irrit_remboursement_tardif_0+irrit_remboursement_tardif_1 AS irrit_remboursement_tardif
  72. ,irrit_absence_reponse_0+irrit_absence_reponse_1+irrit_absence_reponse_2+irrit_absence_reponse_3+irrit_absence_reponse_4+irrit_absence_reponse_5+irrit_absence_reponse_6+irrit_absence_reponse_7+irrit_absence_reponse_8 AS irrit_absence_reponse
  73. ,irrit_difficulte_acces_0+irrit_difficulte_acces_1+irrit_difficulte_acces_2 AS irrit_difficulte_acces
  74. ,irrit_lenteur_traitement_0+irrit_lenteur_traitement_1+irrit_lenteur_traitement_2+irrit_lenteur_traitement_3 AS irrit_lenteur_traitement
  75. ,irrit_contest_index_0+irrit_contest_index_1 AS irrit_contest_index
  76. ,irrit_contest_fact_0+irrit_contest_fact_1+irrit_contest_fact_2+irrit_contest_fact_3+irrit_contest_fact_4+irrit_contest_fact_5+irrit_contest_fact_6+irrit_contest_fact_7+irrit_contest_fact_8+irrit_contest_fact_9 AS irrit_contest_fact
  77. ,irrit_process_fact_0+irrit_process_fact_1+irrit_process_fact_2 AS irrit_process_fact
  78. ,irrit_cheq_ener_0 AS irrit_cheq_ener
  79. ,irrit_redirect_serv_recouvr_0 AS irrit_redirect_serv_recouvr
  80. ,irrit_serv_recouvr_0+irrit_serv_recouvr_1 AS irrit_serv_recouvr
  81. ,irrit_diff_auto_rlv_0 AS irrit_diff_auto_rlv
  82. ,irrit_process_souscription_0+irrit_process_souscription_1+irrit_process_souscription_2 AS irrit_process_souscription
  83. ,irrit_process_mise_en_serv_0+irrit_process_mise_en_serv_1 AS irrit_process_mise_en_serv
  84. ,irrit_absence_recept_fact_0 AS irrit_absence_recept_fact
  85. ,irrit_absence_prel_fact_0+irrit_absence_prel_fact_1 AS irrit_absence_prel_fact
  86. ,irrit_dysfonct_compt_0+irrit_dysfonct_compt_1 AS irrit_dysfonct_compt
  87. ,irrit_resil_non_traite_0+irrit_resil_non_traite_1+irrit_resil_non_traite_2+irrit_resil_non_traite_3+irrit_resil_non_traite_4 AS irrit_resil_non_traite
  88. ,irrit_resil_non_soll_0+irrit_resil_non_soll_1+irrit_resil_non_soll_2 AS irrit_resil_non_soll
  89. ,irrit_process_interv_0+irrit_process_interv_1+irrit_process_interv_2+irrit_process_interv_3 AS irrit_process_interv
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!