Advanced Search

sql server - sanitize url

/ Published in: SQL
Save to your folder(s)

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  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


Comments

You need to login to view comments.