Karen Schaefer

Karen Schaefer

  • 2.1k
  • 17
  • 553

Replace if first character is number- Truncates results

Dec 2 2021 6:57 PM

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
Question Text
sub_form_question_id Question Txt
6933 ¿Tiene el vehículo reflectores de emergencia (triángulos) en el interior?24.007.) Are emergency reflectors (triangles) in the vehicle?
6060 1.) Are Cones Utilized Appropriately?
5610 1.) Comments
6176 1.) OSHA 300 Log Posted (February 1st thru April 30th)?
5968 1.) Site Specific COVID Supervisor/Coordinator?
6619 1.001.) ID del sitio del cliente          1.001.) Customer Site ID
6620 1.002.) Propietario de la torre                      Tower Owner
3782 1.002.) Tower Owner
6621 1.003.) ¿Quien es el cliente?Who is the client
6621 1.003.) ¿Quien es el cliente?Who is the client
4215 1.003.) Who is the customer?
6622 1.004.) ¿Quién es el subcontratista?
6622 1.004.) ¿Quién es el subcontratista?
4216 1.004.) Who is the subcontractor?
3783 1.005.) Crew Foreman - Enter only the Employee ID number if MNS employee. Enter name if it is a GC crew.
6626 1.006.) Número de técnico 2: ingrese solo el número de identificación del empleado si es un empleado de MNS. Ingrese el nombre si es un equipo de GC.Technician #2 - Enter only the Employee ID number if MNS employee. Enter name if it is a GC crew.
3784 1.006.) Technician #2 - Enter only the Employee ID number if MNS employee. Enter name if it is a GC crew.
6626 1.006.) Technician #2 - Enter only the Employee ID number if MNS employee. Enter name if it is a GC crew. For MNS SPG crew - enter only the Foremans employee ID number and attach photo of their training badge. For GC crews - enter the crew foremans name, check spelling against OSHA, Comp. Rigger or Comp. Rescuer cards 
6645 1.007.) Alcance del trabajoIdentifique las actividades específicas en las que participa  el equipo durante el tiempo de la inspección. Seleccione todas las que correspondan.
3803 1.007.) Scope of Work
6079 10.) Are Appropriate Foot Wear Being Worn at all Times When in the Yard?
6194 10.) Are OSHA Regulations and National Electric Codes Current and Readily Available?
5630 10.) Comments

Answers (2)