/*
UPDATE [GB].[ST_BUFFER_ANALYSIS] with split correct values
This Example code is based on the assumption that the order of merged data is:
ANALYTE --> LAB_UNITS --> LAB_METHOD.
  	If it is different, then this logic needs to be adjusted accordingly	
 */
UPDATE [GB].[ST_BUFFER_ANALYSIS]
 SET [ANALYTE]=
	 CASE WHEN (SELECT COUNT(value) FROM  STRING_SPLIT([ANALYTE], '_')) > 1 THEN
				LEFT([ANALYTE],CHARINDEX('_',[ANALYTE])-1)
			ELSE
				[ANALYTE]
			END, 
 [LAB_UNITS] = 
 	   CASE WHEN (SELECT COUNT(value) FROM  STRING_SPLIT([LAB_UNITS], '_')) = 3 THEN 
				SUBSTRING([LAB_UNITS],CHARINDEX('_',[LAB_UNITS])+1, LEN([LAB_UNITS]) 
				- CHARINDEX('_',REVERSE([LAB_UNITS])) 
				- CHARINDEX('_',[LAB_UNITS]			
WHEN (SELECT COUNT(value) FROM  STRING_SPLIT([LAB_UNITS], '_')) = 2 THEN	
				RIGHT([LAB_UNITS],CHARINDEX('_',REVERSE([LAB_UNITS]))-1) 
			WHEN (SELECT COUNT(value) FROM  STRING_SPLIT([LAB_UNITS], '_')) = 1 THEN 
				[LAB_UNITS]
			ELSE 
				NULL 
			END, 
 [LAB_METHOD] = 
       CASE WHEN (SELECT COUNT(value) FROM  STRING_SPLIT([LAB_METHOD], '_')) = 3 THEN 
				RIGHT([LAB_METHOD],CHARINDEX('_',REVERSE([LAB_METHOD]))-1) 
			WHEN (SELECT COUNT(value) FROM  STRING_SPLIT([LAB_METHOD], '_')) = 1 THEN 
				[LAB_METHOD]
			ELSE
				NULL
			END