Image

Funciones Útiles - Formatear una URL en SQL

SQLServer

Hola ABC-eLearners, a modo de ayuda para todos los lectores con ganas de aprender SQL, voy a compartirles mis experiencias, las que considere más útiles para todos. Les presento una función que te ayudara a formatear cualquier texto a un formato valido para URLs.  

CREATE FUNCTION f_ReplaceCharactersURL(
  @VARIA VARCHAR(250)
)
RETURNS VARCHAR(250)
AS
BEGIN
  DECLARE @RESULT VARCHAR(250)

  SET @RESULT = REPLACE(@VARIA,' ','%20')

  SET @RESULT = REPLACE(@RESULT,'!','%21')

  SET @RESULT = REPLACE(@RESULT,'"','%22')

  SET @RESULT = REPLACE(@RESULT,'#','%23')

  SET @RESULT = REPLACE(@RESULT,'$','%24')

  --SET @RESULT = REPLACE(@RESULT,'%','%25')

  SET @RESULT = REPLACE(@RESULT,'&','%26')

  SET @RESULT = REPLACE(@RESULT,'''','%27')

  SET @RESULT = REPLACE(@RESULT,'(','%28')

  SET @RESULT = REPLACE(@RESULT,')','%29')

  SET @RESULT = REPLACE(@RESULT,'*','%2A')

  SET @RESULT = REPLACE(@RESULT,'+','%2B')

  SET @RESULT = REPLACE(@RESULT,'´','%2C')

  SET @RESULT = REPLACE(@RESULT,'-','%2D')

  SET @RESULT = REPLACE(@RESULT,'.','%2E')

  SET @RESULT = REPLACE(@RESULT,'/','%2F')

  SET @RESULT = REPLACE(@RESULT,':',  '%3A')

  SET @RESULT = REPLACE(@RESULT,';',  '%3B')

  SET @RESULT = REPLACE(@RESULT,'<',  '%3C')

  SET @RESULT = REPLACE(@RESULT,'=',  '%3D')

  SET @RESULT = REPLACE(@RESULT,'>',  '%3E')

  SET @RESULT = REPLACE(@RESULT,'?',  '%3F')

  SET @RESULT = REPLACE(@RESULT,'@',  '%40')

  SET @RESULT = REPLACE(@RESULT,'[',  '%5B')

  SET @RESULT = REPLACE(@RESULT,'\',  '%5C')

  SET @RESULT = REPLACE(@RESULT,']',  '%5D')

  SET @RESULT = REPLACE(@RESULT,'^',  '%5E')

  SET @RESULT = REPLACE(@RESULT,'_',  '%5F')

  SET @RESULT = REPLACE(@RESULT,'`',  '%60')

  SET @RESULT = REPLACE(@RESULT,'{',  '%7B')

  SET @RESULT = REPLACE(@RESULT,'|',  '%7C')

  SET @RESULT = REPLACE(@RESULT,'}',  '%7D')

  SET @RESULT = REPLACE(@RESULT,'~',  '%7E')

  SET @RESULT = REPLACE(@RESULT,'`',  '%80')

  SET @RESULT = REPLACE(@RESULT,'‚',  '%82')

  SET @RESULT = REPLACE(@RESULT,'ƒ',  '%83')

  SET @RESULT = REPLACE(@RESULT,'„',  '%84')

  SET @RESULT = REPLACE(@RESULT,'…',  '%85')

  SET @RESULT = REPLACE(@RESULT,'†',  '%86')

  SET @RESULT = REPLACE(@RESULT,'‡',  '%87')

  SET @RESULT = REPLACE(@RESULT,'ˆ',  '%88')

  SET @RESULT = REPLACE(@RESULT,'Š',  '%8A')

  SET @RESULT = REPLACE(@RESULT,'‹',  '%8B')

  SET @RESULT = REPLACE(@RESULT,'Œ',  '%8C')

  SET @RESULT = REPLACE(@RESULT,'Ž',  '%8E')

  SET @RESULT = REPLACE(@RESULT,'“',  '%93')

  SET @RESULT = REPLACE(@RESULT,'”',  '%94')

  SET @RESULT = REPLACE(@RESULT,'•',  '%95')

  SET @RESULT = REPLACE(@RESULT,'–',  '%96')

  SET @RESULT = REPLACE(@RESULT,'—',  '%97')

  SET @RESULT = REPLACE(@RESULT,'˜',  '%98')

  SET @RESULT = REPLACE(@RESULT,'™',  '%99')

  SET @RESULT = REPLACE(@RESULT,'š',  '%9A')

  SET @RESULT = REPLACE(@RESULT,'›',  '%9B')

  SET @RESULT = REPLACE(@RESULT,'œ',  '%9C')

  SET @RESULT = REPLACE(@RESULT,'Ÿ',  '%9F')

  SET @RESULT = REPLACE(@RESULT,'¡',  '%A1')

  SET @RESULT = REPLACE(@RESULT,'¢',  '%A2')

  SET @RESULT = REPLACE(@RESULT,'£',  '%A3')

  SET @RESULT = REPLACE(@RESULT,'¤',  '%A4')

  SET @RESULT = REPLACE(@RESULT,'¥',  '%A5')

  SET @RESULT = REPLACE(@RESULT,'¦',  '%A6')

  SET @RESULT = REPLACE(@RESULT,'§',  '%A7')

  SET @RESULT = REPLACE(@RESULT,'¨',  '%A8')

  SET @RESULT = REPLACE(@RESULT,'©',  '%A9')

  SET @RESULT = REPLACE(@RESULT,'ª',  '%AA')

  SET @RESULT = REPLACE(@RESULT,'«',  '%AB')

  SET @RESULT = REPLACE(@RESULT,'¬',  '%AC')

  SET @RESULT = REPLACE(@RESULT,'®',  '%AE')

  SET @RESULT = REPLACE(@RESULT,'¯',  '%AF')

  SET @RESULT = REPLACE(@RESULT,'°',  '%B0')

  SET @RESULT = REPLACE(@RESULT,'±',  '%B1')

  SET @RESULT = REPLACE(@RESULT,'²',  '%B2')

  SET @RESULT = REPLACE(@RESULT,'³',  '%B3')

  SET @RESULT = REPLACE(@RESULT,'´',  '%B4')

  SET @RESULT = REPLACE(@RESULT,'µ',  '%B5')

  SET @RESULT = REPLACE(@RESULT,'¶',  '%B6')

  SET @RESULT = REPLACE(@RESULT,'·',  '%B7')

  SET @RESULT = REPLACE(@RESULT,'¸',  '%B8')

  SET @RESULT = REPLACE(@RESULT,'¹',  '%B9')

  SET @RESULT = REPLACE(@RESULT,'º',  '%BA')

  SET @RESULT = REPLACE(@RESULT,'»',  '%BB')

  SET @RESULT = REPLACE(@RESULT,'¼',  '%BC')

  SET @RESULT = REPLACE(@RESULT,'½',  '%BD')

  SET @RESULT = REPLACE(@RESULT,'¾',  '%BE')

  SET @RESULT = REPLACE(@RESULT,'¿',  '%BF')

  SET @RESULT = REPLACE(@RESULT,'À',  '%C0')

  SET @RESULT = REPLACE(@RESULT,'Á',  '%C1')

  SET @RESULT = REPLACE(@RESULT,'Â',  '%C2')

  SET @RESULT = REPLACE(@RESULT,'Ã',  '%C3')

  SET @RESULT = REPLACE(@RESULT,'Ä',  '%C4')

  SET @RESULT = REPLACE(@RESULT,'Å',  '%C5')

  SET @RESULT = REPLACE(@RESULT,'Æ',  '%C6')

  SET @RESULT = REPLACE(@RESULT,'Ç',  '%C7')

  SET @RESULT = REPLACE(@RESULT,'È',  '%C8')

  SET @RESULT = REPLACE(@RESULT,'É',  '%C9')

  SET @RESULT = REPLACE(@RESULT,'Ê',  '%CA')

  SET @RESULT = REPLACE(@RESULT,'Ë',  '%CB')

  SET @RESULT = REPLACE(@RESULT,'Ì',  '%CC')

  SET @RESULT = REPLACE(@RESULT,'Í',  '%CD')

  SET @RESULT = REPLACE(@RESULT,'Î',  '%CE')

  SET @RESULT = REPLACE(@RESULT,'Ï',  '%CF')

  SET @RESULT = REPLACE(@RESULT,'Ð',  '%D0')

  SET @RESULT = REPLACE(@RESULT,'Ñ',  '%D1')

  SET @RESULT = REPLACE(@RESULT,'Ò',  '%D2')

  SET @RESULT = REPLACE(@RESULT,'Ó',  '%D3')

  SET @RESULT = REPLACE(@RESULT,'Ô',  '%D4')

  SET @RESULT = REPLACE(@RESULT,'Õ',  '%D5')

  SET @RESULT = REPLACE(@RESULT,'Ö',  '%D6')

  SET @RESULT = REPLACE(@RESULT,'×',  '%D7')

  SET @RESULT = REPLACE(@RESULT,'Ø',  '%D8')

  SET @RESULT = REPLACE(@RESULT,'Ù',  '%D9')

  SET @RESULT = REPLACE(@RESULT,'Ú',  '%DA')

  SET @RESULT = REPLACE(@RESULT,'Û',  '%DB')

  SET @RESULT = REPLACE(@RESULT,'Ü',  '%DC')

  SET @RESULT = REPLACE(@RESULT,'Ý',  '%DD')

  SET @RESULT = REPLACE(@RESULT,'Þ',  '%DE')

  SET @RESULT = REPLACE(@RESULT,'ß',  '%DF')

  SET @RESULT = REPLACE(@RESULT,'à',  '%E0')

  SET @RESULT = REPLACE(@RESULT,'á',  '%E1')

  SET @RESULT = REPLACE(@RESULT,'â',  '%E2')

  SET @RESULT = REPLACE(@RESULT,'ã',  '%E3')

  SET @RESULT = REPLACE(@RESULT,'ä',  '%E4')

  SET @RESULT = REPLACE(@RESULT,'å',  '%E5')

  SET @RESULT = REPLACE(@RESULT,'æ',  '%E6')

  SET @RESULT = REPLACE(@RESULT,'ç',  '%E7')

  SET @RESULT = REPLACE(@RESULT,'è',  '%E8')

  SET @RESULT = REPLACE(@RESULT,'é',  '%E9')

  SET @RESULT = REPLACE(@RESULT,'ê',  '%EA')

  SET @RESULT = REPLACE(@RESULT,'ë',  '%EB')

  SET @RESULT = REPLACE(@RESULT,'ì',  '%EC')

  SET @RESULT = REPLACE(@RESULT,'í',  '%ED')

  SET @RESULT = REPLACE(@RESULT,'î',  '%EE')

  SET @RESULT = REPLACE(@RESULT,'ï',  '%EF')

  SET @RESULT = REPLACE(@RESULT,'ð',  '%F0')

  SET @RESULT = REPLACE(@RESULT,'ñ',  '%F1')

  SET @RESULT = REPLACE(@RESULT,'ò',  '%F2')

  SET @RESULT = REPLACE(@RESULT,'ó',  '%F3')

  SET @RESULT = REPLACE(@RESULT,'ô',  '%F4')

  SET @RESULT = REPLACE(@RESULT,'õ',  '%F5')

  SET @RESULT = REPLACE(@RESULT,'ö',  '%F6')

  SET @RESULT = REPLACE(@RESULT,'÷',  '%F7')

  SET @RESULT = REPLACE(@RESULT,'ø',  '%F8')

  SET @RESULT = REPLACE(@RESULT,'ù',  '%F9')

  SET @RESULT = REPLACE(@RESULT,'ú',  '%FA')

  SET @RESULT = REPLACE(@RESULT,'û',  '%FB')

  SET @RESULT = REPLACE(@RESULT,'ü',  '%FC')

  SET @RESULT = REPLACE(@RESULT,'ý',  '%FD')

  SET @RESULT = REPLACE(@RESULT,'þ',  '%FE')

  SET @RESULT = REPLACE(@RESULT,'ÿ',  '%FF')

  RETURN @RESULT
END 


Ejemplo:

SELECT DBO.f_ReplaceCharactersURL('campaña-deremates del año')
--Resultado: campa%D1a%2Dderemates%20del%20a%D1o%20


Comenta qué otras funciones más necesitarías para reusar y optimizar tu tiempo mientras programas en Base de datos.

0 Commentario(s)

Deja un comentario