EBOI
10/5/2018 - 11:07 AM

Usuwanie nieprawidłowych znaków z pełnej nazwy użytkownika

Znaki & oraz < zapisane w pliku xml uniemożliwiają wygenerowanie wydruku w oparciu o xslt. Mapowane do xml pole musi zostać pozbawione tych znaków

USE DATABASE

DECLARE @MyCursor CURSOR;

DECLARE @Id uniqueidentifier;
DECLARE @cus_FullName VARCHAR(MAX);
---------------------------------------------------------------------------------------------------------------------------------------------------
Declare @RmCharacter as varchar(3)
---------------------------------------------------------------------------------------------------------------------------------------------------
-- [Id] - Pole z identyfikatorem krotki, [cus_FullName] - Pole zawierające znaki specjalne, [dbo].[Customer] - Rozpatrywana tabela
---------------------------------------------------------------------------------------------------------------------------------------------------
BEGIN
    SET @MyCursor = CURSOR FOR
    select [cus_ID], [cus_FullName] from [dbo].[Customer]
	WHERE [cus_FullName] like '%&%' OR [cus_FullName] like '%<%' OR [cus_FullName] like '%>%'

    OPEN @MyCursor 
    FETCH NEXT FROM @MyCursor 
    INTO @Id, @cus_FullName

    WHILE @@FETCH_STATUS = 0
    BEGIN
	   Set @RmCharacter = '&'
	   Set @cus_FullName = REPLACE(@cus_FullName,@RmCharacter,'')
	   Set @RmCharacter = '<'
	   Set @cus_FullName = REPLACE(@cus_FullName,@RmCharacter,'')
	   Set @RmCharacter = '>'
	   Set @cus_FullName = REPLACE(@cus_FullName,@RmCharacter,'')

		UPDATE [dbo].[Customer]
		SET [cus_FullName] = @cus_FullName
		WHERE [cus_ID] = @Id

      FETCH NEXT FROM @MyCursor 
      INTO @Id, @cus_FullName
    END; 

    CLOSE @MyCursor;
    DEALLOCATE @MyCursor;
END;