Posted By

dactivo on 08/19/11


Tagged


Versions (?)

sql server - sanitize url


 / Published in: SQL
 

  1. SET ANSI_NULLS ON
  2. GO
  3. SET QUOTED_IDENTIFIER ON
  4. GO
  5. -- =============================================
  6. -- Author: <Author,,Name>
  7. -- Create date: <Create Date,,>
  8. -- Description: <Description,,>
  9. -- =============================================
  10. ALTER PROCEDURE dbo.CrearURLNoticias
  11. AS
  12. BEGIN
  13.  
  14. declare @titulo varchar(500),@titulofinal nvarchar(255),
  15. @s1 nvarchar(255),@s2 nvarchar(255), @anterior nvarchar(1), @nuevo nvarchar(1)
  16. ,@iter int,@permitidos nvarchar(255)
  17.  
  18. SET @s1= 'ÁÂÃÄÉÊËÍÎÏÓÕÔÖÙÛÚÜàáäâãèéëêîìïíòöóõôùûúü'
  19. SET @s2 = 'AAAAEEEIIIOOOOUUUUaaaaaeeeeiiiiooooouuuu'
  20. SET @permitidos='abcdefghjklmnopqrstuvwxyz0123456789-'
  21.  
  22. DECLARE Datos CURSOR FOR
  23. SELECT title FROM NewsPages WHERE titleUrl IS NULL OR ltrim(rtrim(titleUrl)) =''
  24.  
  25. OPEN Datos
  26. FETCH NEXT FROM Datos INTO @titulo
  27.  
  28. WHILE ((@@FETCH_STATUS = 0))
  29. BEGIN
  30. SET @titulofinal=@titulo
  31. SET @titulofinal=Ltrim(RTrim(@titulofinal))
  32. SET @titulofinal=REPLACE(@titulofinal,' ','-')
  33.  
  34. SET @titulofinal=REPLACE(@titulofinal,'ñ','nh')
  35. SET @titulofinal=REPLACE(@titulofinal,'&aacute;','a')
  36. SET @titulofinal=REPLACE(@titulofinal,'&eacute;','e')
  37. SET @titulofinal=REPLACE(@titulofinal,'&iacute;','i')
  38. SET @titulofinal=REPLACE(@titulofinal,'&oacute;','o')
  39. SET @titulofinal=REPLACE(@titulofinal,'&uacute;','u')
  40. SET @titulofinal=REPLACE(@titulofinal,'&ntilde;','nh')
  41.  
  42.  
  43. SET @iter = 0
  44. IF (len(@titulofinal) != 0)
  45. begin
  46. WHILE @iter<len(@s1)
  47. begin
  48. SET @anterior = substring(@s1,@iter,1);
  49. SET @nuevo = substring(@s2,@iter,1);
  50. SET @titulofinal = REPLACE(@titulofinal,@anterior, @nuevo);
  51. SET @iter=@iter+1
  52. end
  53. end
  54.  
  55. SET @titulofinal=Lower(@titulofinal)
  56.  
  57.  
  58. While PatIndex('%[^a-zA-Z0-9 -]%', @titulofinal) > 0
  59. SET @titulofinal = Stuff(@titulofinal, PatIndex('%[^a-zA-Z0-9 -]%', @titulofinal), 1, '')
  60.  
  61.  
  62. print @titulofinal
  63.  
  64.  
  65.  
  66.  
  67. FETCH NEXT FROM Datos INTO @titulo
  68. END
  69.  
  70. CLOSE Datos
  71.  
  72. DEALLOCATE Datos
  73.  
  74. end
  75.  
  76. GO

Report this snippet  

You need to login to post a comment.