MS-SQL - strip html
/SQL/MS-SQL - strip html.sql
-- minimal strip html :
DECLARE @info_id AS NUMERIC(18)
DECLARE @info_valeur AS VARCHAR(MAX)
DECLARE @info_valeur_init AS VARCHAR(MAX)
DECLARE @info_valeur_old AS VARCHAR(MAX)
DECLARE infos CURSOR GLOBAL FORWARD_ONLY
FOR
SELECT info_id, info_valeur FROM information WHERE firu_id = 1040
OPEN infos
FETCH NEXT FROM infos INTO @info_id, @info_valeur
WHILE (@@FETCH_STATUS <> -1)
BEGIN
SET @info_valeur_init = @info_valeur
SET @info_valeur = REPLACE(@info_valeur, CHAR(9), ' ') -- tab
SET @info_valeur = REPLACE(@info_valeur, CHAR(13)+CHAR(10), ' ')
SET @info_valeur = REPLACE(@info_valeur, CHAR(13), ' ')
SET @info_valeur = REPLACE(@info_valeur, CHAR(10), ' ')
SET @info_valeur = REPLACE(@info_valeur, '<b>', '')
SET @info_valeur = REPLACE(@info_valeur, '</b>', '')
SET @info_valeur = REPLACE(@info_valeur, '<i>', '')
SET @info_valeur = REPLACE(@info_valeur, '</i>', '')
SET @info_valeur = REPLACE(@info_valeur, '<br>', '')
SET @info_valeur = REPLACE(@info_valeur, '<br/>', '')
SET @info_valeur = REPLACE(@info_valeur, '<br />', '')
SET @info_valeur = REPLACE(@info_valeur, '…', '...')
SET @info_valeur = REPLACE(@info_valeur, '’', '''')
-- suppr les doublons d'espace :
SET @info_valeur_old=''
WHILE (@info_valeur<>@info_valeur_old)
BEGIN
SET @info_valeur_old = @info_valeur
SET @info_valeur = REPLACE(@info_valeur, ' ', ' ')
END
IF (@info_valeur <> @info_valeur_init)
UPDATE information SET info_valeur = @info_valeur WHERE info_id = @info_id
FETCH NEXT FROM infos INTO @info_id, @info_valeur
END
CLOSE infos
DEALLOCATE infos