I need to clean up some data (question text) where is may begin with a numeric characters (ex. 11.006.)), if true then remove everything to the left of the ".)". else display existing question as is, However, my current attempt seems to truncate the results
What do I need to modify to handle this issue?
Thanks,
Karen
Here is my current code:
/****** Script for SelectTopNRows command from SSMS ******/ SELECT Distinct([sub_form_question_id]),[sub_form_question_code],[sub_form_question_system_code],[sub_form_question_field_type],dbo.udf_StripHTML([question_text]) as question_textFROM [MNS_DonesafeDB].[stg].[Module_Results] -- Where sub_form_question_id = '2916' order by [sub_form_question_id] asc
/****** Object: UserDefinedFunction [dbo].[udf_StripHTML] Script Date: 12/2/2021 1:37:28 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GOALTER FUNCTION [dbo].[udf_StripHTML] (@HTMLText VARCHAR(MAX)) RETURNS VARCHAR(MAX) AS BEGIN DECLARE @Start INT DECLARE @End INT DECLARE @Length INT SET @Start = CHARINDEX('<',@HTMLText) SET @End = CHARINDEX('>',@HTMLText,CHARINDEX('<',@HTMLText)) SET @Length = (@End - @Start) + 1 WHILE @Start > 0 AND @End > 0 AND @Length > 0 BEGIN SET @HTMLText = STUFF(@HTMLText,@Start,@Length,'') SET @Start = CHARINDEX('<',@HTMLText) SET @End = CHARINDEX('>',@HTMLText,CHARINDEX('<',@HTMLText)) SET @Length = (@End - @Start) + 1 END --RETURN LTRIM(RTRIM(@HTMLText)) --RETURN replace(replace(LTRIM(RTRIM(@HTMLText)),' ',' '), ''', '')RETURN REPLACE(SUBSTRING(replace(replace(LTRIM(RTRIM(@HTMLText)),' ',' '), ''', ''), CHARINDEX('.)', replace(replace(LTRIM(RTRIM(@HTMLText)),' ',' '), ''', '')), LEN(replace(replace(LTRIM(RTRIM(@HTMLText)),' ',' '), ''', ''))), '.) ', '') END