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

RSS Icon Subscribe to comments

You need to login to post a comment.