Esta função já foi chamada de limpa_string ai mudei para limpa_palavra mas a função é a mesma.
create function limpa_palavra (@palavra varchar (max)) returns varchar(max)
begin
declare @pos int
declare @String varchar(max)
set @String = @palavra
/****************************************************************************************************************/
/** RETIRA ACENTUAÇÃO DAS VOGAIS **/
/****************************************************************************************************************/
/*
SET @String = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@String,'À','A'),'Á','A'),'Â','A'),'Ã','A'),'Ä','A')
SET @String = REPLACE(REPLACE(@String,'Å','A'),'Æ','A')
SET @String = REPLACE(REPLACE(REPLACE(REPLACE(@String,'È','E'),'É','E'),'Ê','E'),'Ë','E')
SET @String = REPLACE(REPLACE(REPLACE(REPLACE(@String,'Ì','I'),'Í','I'),'Î','I'),'Ï','I')
SET @String = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@String,'Ò','o'),'Ó','o'),'Ô','o'),'Õ','o'),'Ö','o')
SET @String = REPLACE(REPLACE(REPLACE(REPLACE(@String,'Ù','U'),'Ú','U'),'Û','U'),'Ü','U')
*/
SET @String = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@String,'á','a'),'à','a'),'â','a'),'ã','a'),'ä','a')
SET @String = REPLACE(REPLACE(REPLACE(REPLACE(@String,'é','e'),'è','e'),'ê','e'),'ë','e')
SET @String = REPLACE(REPLACE(REPLACE(REPLACE(@String,'í','i'),'ì','i'),'î','i'),'ï','i')
SET @String = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@String,'ó','o'),'ò','o'),'ô','o'),'õ','o'),'ö','o')
SET @String = REPLACE(REPLACE(REPLACE(REPLACE(@String,'ú','u'),'ù','u'),'û','u'),'ü','u')
/****************************************************************************************************************/
/** RETIRA ACENTUAÇÃO DAS CONSOANTES **/
/****************************************************************************************************************/
SET @String = REPLACE(@String,'ý','y')
SET @String = REPLACE(@String,'ÿ','y')
SET @String = REPLACE(@String,'Ý','Y')
SET @String = REPLACE(@String,'ñ','n')
SET @String = REPLACE(@String,'ç','c')
SET @String = REPLACE(@String,'Ç','C')
SET @String = REPLACE(@String,'Ñ','N')
/****************************************************************************************************************/
/** RETIRA CARACTERES ESPECIAIS - "IMPRIMIVEIS" **/
/****************************************************************************************************************/
SET @String=ISNULL(@String,' ')
SET @String=REPLACE(@String,';',',')
SET @String=REPLACE(@String,'''',' ')
SET @String=REPLACE(@String,'/',' ')
SET @String=REPLACE(@String,CHAR(09),' ')
SET @String=REPLACE(@String,CHAR(10),' ')
SET @String=REPLACE(@String,CHAR(13),' ')
SET @String=REPLACE(@String,'ª',' ')
/****************************************************************************************************************/
/** RETIRA CARACTERES ESPECIAIS - INICIO TABELA ASC IMPRIMÍVEIS **/
/****************************************************************************************************************/
SET @String = (SELECT REPLACE(@String, CHAR(1),'')) -- 01 1 SOH ^A Start of Header - Início do cabeçalho
SET @String = (SELECT REPLACE(@String, CHAR(2),'')) -- 02 2 STX ^B Start of Text - Início do texto
SET @String = (SELECT REPLACE(@String, CHAR(3),''))
SET @String = (SELECT REPLACE(@String, CHAR(4),''))
SET @String = (SELECT REPLACE(@String, CHAR(5),''))
SET @String = (SELECT REPLACE(@String, CHAR(6),''))
SET @String = (SELECT REPLACE(@String, CHAR(7),'')) -- 07 7 BEL ^G Bell - Campainha
SET @String = (SELECT REPLACE(@String, CHAR(8),''))
SET @String = (SELECT REPLACE(@String, CHAR(9),'')) -- 09 9 HT ^I Horizontal Tabulation - Tabulação horizontal
SET @String = (SELECT REPLACE(@String, CHAR(10),'')) -- 0A 10 LF ^J Line-Feed - Alimenta linha
SET @String = (SELECT REPLACE(@String, CHAR(11),'')) -- 0B 11 VT ^K Vertical Tabulation - Tabulação vertical
SET @String = (SELECT REPLACE(@String, CHAR(12),'')) -- 0C 12 FF ^L Form-Feed - Alimenta formulário
SET @String = (SELECT REPLACE(@String, CHAR(13),'')) -- 13 19 DC3 ^S Device-Control 3
SET @String = (SELECT REPLACE(@String, CHAR(14),''))
SET @String = (SELECT REPLACE(@String, CHAR(15),''))
SET @String = (SELECT REPLACE(@String, CHAR(16),''))
SET @String = (SELECT REPLACE(@String, CHAR(17),''))
SET @String = (SELECT REPLACE(@String, CHAR(18),''))
SET @String = (SELECT REPLACE(@String, CHAR(19),''))
SET @String = (SELECT REPLACE(@String, CHAR(20),''))
SET @String = (SELECT REPLACE(@String, CHAR(21),''))
SET @String = (SELECT REPLACE(@String, CHAR(22),''))
SET @String = (SELECT REPLACE(@String, CHAR(23),''))
SET @String = (SELECT REPLACE(@String, CHAR(24),'')) -- 18 24 CAN ^X Cancel
SET @String = (SELECT REPLACE(@String, CHAR(25),''))
SET @String = (SELECT REPLACE(@String, CHAR(26),''))
SET @String = (SELECT REPLACE(@String, CHAR(27),'')) -- 1B 27 ESC ^[ Escape
SET @String = (SELECT REPLACE(@String, CHAR(28),''))
SET @String = (SELECT REPLACE(@String, CHAR(29),''))
SET @String = (SELECT REPLACE(@String, CHAR(30),''))
SET @String = (SELECT REPLACE(@String, CHAR(31),''))
/****************************************************************************************************************/
/** RETIRA CARACTERES ESPECIAIS - INICIO TABELA ASC EXTENDIDA **/
/****************************************************************************************************************/
SET @String = REPLACE(@String,CHAR(126),'') -- 126 char = ~
SET @String = REPLACE(@String,CHAR(127),'') -- 127 char =
SET @String = REPLACE(@String,CHAR(128),'e') -- 128 char = €
SET @String = REPLACE(@String,CHAR(129),'') -- 129 char =
SET @String = REPLACE(@String,CHAR(130),'') -- 130 char = ‚
SET @String = REPLACE(@String,CHAR(131),'f') -- 131 char = ƒ
SET @String = REPLACE(@String,CHAR(132),'') -- 132 char = „
SET @String = REPLACE(@String,CHAR(133),'') -- 133 char = …
SET @String = REPLACE(@String,CHAR(134),'t') -- 134 char = †
SET @String = REPLACE(@String,CHAR(135),'c') -- 135 char = ‡
SET @String = REPLACE(@String,CHAR(136),'') -- 136 char = ˆ
SET @String = REPLACE(@String,CHAR(137),'') -- 137 char = ‰
SET @String = REPLACE(@String,CHAR(138),'s') -- 138 char = Š
SET @String = REPLACE(@String,CHAR(139),'') -- 139 char = ‹
SET @String = REPLACE(@String,CHAR(140),'ce') -- 140 char = Œ
SET @String = REPLACE(@String,CHAR(141),'') -- 141 char =
SET @String = REPLACE(@String,CHAR(142),'z') -- 142 char = Ž
SET @String = REPLACE(@String,CHAR(143),'') -- 143 char =
SET @String = REPLACE(@String,CHAR(144),'') -- 144 char =
SET @String = REPLACE(@String,CHAR(145),'') -- 145 char = ‘
SET @String = REPLACE(@String,CHAR(146),'') -- 146 char = ’
SET @String = REPLACE(@String,CHAR(147),'') -- 147 char = “
SET @String = REPLACE(@String,CHAR(148),'') -- 148 char = ”
SET @String = REPLACE(@String,CHAR(149),'') -- 149 char = •
SET @String = REPLACE(@String,CHAR(150),'') -- 150 char = –
SET @String = REPLACE(@String,CHAR(151),'') -- 151 char = —
SET @String = REPLACE(@String,CHAR(152),'') -- 152 char = ˜
SET @String = REPLACE(@String,CHAR(153),'') -- 153 char = ™
SET @String = REPLACE(@String,CHAR(154),'s') -- 154 char = š
SET @String = REPLACE(@String,CHAR(155),'') -- 155 char = ›
SET @String = REPLACE(@String,CHAR(156),'ce') -- 156 char = œ
SET @String = REPLACE(@String,CHAR(157),'') -- 157 char =
SET @String = REPLACE(@String,CHAR(158),'z') -- 158 char = ž
SET @String = REPLACE(@String,CHAR(159),'Y') -- 159 char = Ÿ
SET @String = REPLACE(@String,CHAR(160),'') -- 160 char =
SET @String = REPLACE(@String,CHAR(161),'') -- 161 char = ¡
SET @String = REPLACE(@String,CHAR(162),'c') -- 162 char = ¢
SET @String = REPLACE(@String,CHAR(163),'l') -- 163 char = £
SET @String = REPLACE(@String,CHAR(164),'') -- 164 char = ¤
SET @String = REPLACE(@String,CHAR(165),'y') -- 165 char = ¥
SET @String = REPLACE(@String,CHAR(166),'') -- 166 char = ¦
SET @String = REPLACE(@String,CHAR(167),'ss') -- 167 char = §
SET @String = REPLACE(@String,CHAR(168),'') -- 168 char = ¨
SET @String = REPLACE(@String,CHAR(169),'') -- 169 char = ©
SET @String = REPLACE(@String,CHAR(170),'a') -- 170 char = ª
SET @String = REPLACE(@String,CHAR(171),'') -- 171 char = «
SET @String = REPLACE(@String,CHAR(172),'') -- 172 char = ¬
SET @String = REPLACE(@String,CHAR(173),'') -- 173 char =
SET @String = REPLACE(@String,CHAR(174),'') -- 174 char = ®
SET @String = REPLACE(@String,CHAR(175),'') -- 175 char = ¯
SET @String = REPLACE(@String,CHAR(176),'o') -- 176 char = °
SET @String = REPLACE(@String,CHAR(177),'') -- 177 char = ±
SET @String = REPLACE(@String,CHAR(178),'') -- 178 char = ²
SET @String = REPLACE(@String,CHAR(179),'') -- 179 char = ³
SET @String = REPLACE(@String,CHAR(180),'') -- 180 char = ´
SET @String = REPLACE(@String,CHAR(181),'u') -- 181 char = µ
SET @String = REPLACE(@String,CHAR(182),'') -- 182 char = ¶
SET @String = REPLACE(@String,CHAR(183),'') -- 183 char = ·
SET @String = REPLACE(@String,CHAR(184),'') -- 184 char = ¸
SET @String = REPLACE(@String,CHAR(185),'i') -- 185 char = ¹
SET @String = REPLACE(@String,CHAR(186),'o') -- 186 char = º
SET @String = REPLACE(@String,CHAR(187),'') -- 187 char = »
SET @String = REPLACE(@String,CHAR(188),'') -- 188 char = ¼
SET @String = REPLACE(@String,CHAR(189),'') -- 189 char = ½
SET @String = REPLACE(@String,CHAR(190),'') -- 190 char = ¾
SET @String = REPLACE(@String,CHAR(191),'') -- 191 char = ¿
SET @String = REPLACE(@String,CHAR(192),'A') -- 192 char = À
SET @String = REPLACE(@String,CHAR(193),'A') -- 193 char = Á
SET @String = REPLACE(@String,CHAR(194),'A') -- 194 char = Â
SET @String = REPLACE(@String,CHAR(195),'A') -- 195 char = Ã
SET @String = REPLACE(@String,CHAR(196),'A') -- 196 char = Ä
SET @String = REPLACE(@String,CHAR(197),'A') -- 197 char = Å
SET @String = REPLACE(@String,CHAR(198),'A') -- 198 char = Æ
SET @String = REPLACE(@String,CHAR(199),'C') -- 199 char = Ç
SET @String = REPLACE(@String,CHAR(200),'E') -- 200 char = È
SET @String = REPLACE(@String,CHAR(201),'E') -- 201 char = É
SET @String = REPLACE(@String,CHAR(202),'E') -- 202 char = Ê
SET @String = REPLACE(@String,CHAR(203),'E') -- 203 char = Ë
SET @String = REPLACE(@String,CHAR(204),'I') -- 204 char = Ì
SET @String = REPLACE(@String,CHAR(205),'I') -- 205 char = Í
SET @String = REPLACE(@String,CHAR(206),'I') -- 206 char = Î
SET @String = REPLACE(@String,CHAR(207),'I') -- 207 char = Ï
SET @String = REPLACE(@String,CHAR(208),'d') -- 208 char = Ð
SET @String = REPLACE(@String,CHAR(209),'N') -- 209 char = Ñ
SET @String = REPLACE(@String,CHAR(210),'O') -- 210 char = Ò
SET @String = REPLACE(@String,CHAR(211),'O') -- 211 char = Ó
SET @String = REPLACE(@String,CHAR(212),'O') -- 212 char = Ô
SET @String = REPLACE(@String,CHAR(213),'O') -- 213 char = Õ
SET @String = REPLACE(@String,CHAR(214),'O') -- 214 char = Ö
SET @String = REPLACE(@String,CHAR(215),'x') -- 215 char = ×
SET @String = REPLACE(@String,CHAR(216),'o') -- 216 char = Ø
SET @String = REPLACE(@String,CHAR(217),'U') -- 217 char = Ù
SET @String = REPLACE(@String,CHAR(218),'U') -- 218 char = Ú
SET @String = REPLACE(@String,CHAR(219),'U') -- 219 char = Û
SET @String = REPLACE(@String,CHAR(220),'U') -- 220 char = Ü
SET @String = REPLACE(@String,CHAR(221),'y') -- 221 char = Ý
SET @String = REPLACE(@String,CHAR(222),'th') -- 222 char = Þ
SET @String = REPLACE(@String,CHAR(223),'ss') -- 223 char = ß
SET @String = REPLACE(@String,CHAR(224),'a') -- 224 char = à
SET @String = REPLACE(@String,CHAR(225),'a') -- 225 char = á
SET @String = REPLACE(@String,CHAR(226),'a') -- 226 char = â
SET @String = REPLACE(@String,CHAR(227),'a') -- 227 char = ã
SET @String = REPLACE(@String,CHAR(228),'a') -- 228 char = ä
SET @String = REPLACE(@String,CHAR(229),'a') -- 229 char = å
SET @String = REPLACE(@String,CHAR(230),'ae') -- 230 char = æ
SET @String = REPLACE(@String,CHAR(231),'c') -- 231 char = ç
SET @String = REPLACE(@String,CHAR(232),'e') -- 232 char = è
SET @String = REPLACE(@String,CHAR(233),'e') -- 233 char = é
SET @String = REPLACE(@String,CHAR(234),'e') -- 234 char = ê
SET @String = REPLACE(@String,CHAR(235),'e') -- 235 char = ë
SET @String = REPLACE(@String,CHAR(236),'i') -- 236 char = ì
SET @String = REPLACE(@String,CHAR(237),'i') -- 237 char = í
SET @String = REPLACE(@String,CHAR(238),'i') -- 238 char = î
SET @String = REPLACE(@String,CHAR(239),'i') -- 239 char = ï
SET @String = REPLACE(@String,CHAR(240),'o') -- 240 char = ð
SET @String = REPLACE(@String,CHAR(241),'n') -- 241 char = ñ
SET @String = REPLACE(@String,CHAR(242),'o') -- 242 char = ò
SET @String = REPLACE(@String,CHAR(243),'o') -- 243 char = ó
SET @String = REPLACE(@String,CHAR(244),'o') -- 244 char = ô
SET @String = REPLACE(@String,CHAR(245),'o') -- 245 char = õ
SET @String = REPLACE(@String,CHAR(246),'o') -- 246 char = ö
SET @String = REPLACE(@String,CHAR(247),'') -- 247 char = ÷
SET @String = REPLACE(@String,CHAR(248),'o') -- 248 char = ø
SET @String = REPLACE(@String,CHAR(249),'u') -- 249 char = ù
SET @String = REPLACE(@String,CHAR(250),'u') -- 250 char = ú
SET @String = REPLACE(@String,CHAR(251),'u') -- 251 char = û
SET @String = REPLACE(@String,CHAR(252),'u') -- 252 char = ü
SET @String = REPLACE(@String,CHAR(253),'y') -- 253 char = ý
SET @String = REPLACE(@String,CHAR(254),'th') -- 254 char = þ
SET @String = REPLACE(@String,CHAR(255),'y') -- 255 char = ÿ
RETURN @String
end