Advanced Search

Return to Snippet

Revision: 50406
at August 19, 2011 21:29 by dactivo
Initial Code 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
alter PROCEDURE dbo.CrearURLNoticias
AS
BEGIN

declare @titulo varchar(500),@titulofinal nvarchar(255), 
@s1 nvarchar(255),@s2 nvarchar(255), @anterior nvarchar(1), @nuevo nvarchar(1)
,@iter int,@permitidos nvarchar(255)
				
			set @s1= 'ÁÂÃÄÉÊËÍÎÏÓÕÔÖÙÛÚÜàáäâãèéëêîìïíòöóõôùûúü'
            set @s2 = 'AAAAEEEIIIOOOOUUUUaaaaaeeeeiiiiooooouuuu'
            set @permitidos='abcdefghjklmnopqrstuvwxyz0123456789-'
    
    DECLARE Datos CURSOR  FOR 
    select title from NewsPages where titleUrl is null or ltrim(rtrim(titleUrl)) =''
    
    OPEN Datos
	FETCH NEXT FROM Datos INTO @titulo
	
			WHILE ((@@FETCH_STATUS = 0))
			BEGIN
				set @titulofinal=@titulo
					set @titulofinal=Ltrim(RTrim(@titulofinal))
				set @titulofinal=REPLACE(@titulofinal,' ','-')
				
				set @titulofinal=REPLACE(@titulofinal,'ñ','nh')
				set @titulofinal=REPLACE(@titulofinal,'&aacute;','a')
				set @titulofinal=REPLACE(@titulofinal,'&eacute;','e')
				set @titulofinal=REPLACE(@titulofinal,'&iacute;','i')
				set @titulofinal=REPLACE(@titulofinal,'&oacute;','o')
				set @titulofinal=REPLACE(@titulofinal,'&uacute;','u')
				set @titulofinal=REPLACE(@titulofinal,'&ntilde;','nh')
				
				
				set @iter = 0
				if (len(@titulofinal) != 0)
				begin
					WHILE @iter<len(@s1)
					begin
						set @anterior = substring(@s1,@iter,1);
						set @nuevo = substring(@s2,@iter,1);
						set @titulofinal = Replace(@titulofinal,@anterior, @nuevo);
						set @iter=@iter+1						
					end
				end
					
				set @titulofinal=Lower(@titulofinal)
				
				
				  While PatIndex('%[^a-zA-Z0-9 -]%', @titulofinal) > 0
				  Set @titulofinal = Stuff(@titulofinal, PatIndex('%[^a-zA-Z0-9 -]%', @titulofinal), 1, '')
        
        
			print @titulofinal
			
			
			
			
			FETCH NEXT FROM Datos INTO @titulo
			END
			
	CLOSE Datos

DEALLOCATE Datos
    
    end
    
GO
Initial URL 

                                

                                                            

                                    

                                        

                                        Initial Description
                                        

                                    

                                    

                                

                                                            

                                    

                                        

                                        Initial Title
                                        

                                    

                                    
sql server - sanitize url

                                

                                                            

                                    

                                        

                                        Initial Tags
                                        

                                    

                                    

                                

                                                            

                                    

                                        

                                        Initial Language
                                        

                                    

                                    
SQL