String Manipulation

Transact-SQL
1

Hello Experts,

I have a data in a table as shown in the first column (A) and would like to update as shown in column B.

Please note there is a space on both sides of the operator (+, -, /, *).

Only these four arithmetic operators would be used. Except, operators and numerals, other words should be enclosed by .

Please do help me to achieve this either using query or using user defined function or stored procedure.

image

Thanks,
Naveen

2

Here some code; it is not final , but almost there;

;with cte
as (
	select 'Total Deduction * 13' as a
	union all select 'Pre Results + 10 - New Results'
	union all select 'Premium + 0.01'
	union all select '100 / Final Results'
)
,rec AS
(
	select 
			0 as lvl
			,a AS word	
			,cast(a as varchar(500)) as remainInit		
			,patindex('%[a-zA-Z]%',a) as startPrimul
			,CASE 
				WHEN LEN(a) > 0 and patindex('%[a-zA-Z]%',a) > 0 and patindex('%[a-zA-Z][ ][-+*/]%',a) > 0 THEN patindex('%[a-zA-Z][ ][-+*/]%',a)
				WHEN LEN(a) > 0 and patindex('%[a-zA-Z]%',a) > 0 and patindex('%[a-zA-Z][ ][-+*/]%',a) = 0 THEN LEN(a)
				ELSE 0 
			 END as endPrimul
			,patindex('%[a-zA-Z]%',a) as startTotal
			,CASE 
				WHEN LEN(a) > 0 and patindex('%[a-zA-Z]%',a) > 0 and patindex('%[a-zA-Z][ ][-+*/]%',a) > 0 THEN patindex('%[a-zA-Z][ ][-+*/]%',a)
				WHEN LEN(a) > 0 and patindex('%[a-zA-Z]%',a) > 0 and patindex('%[a-zA-Z][ ][-+*/]%',a) = 0 THEN LEN(a)
				ELSE 0 
			 END as endTotal
			,cast(substring(a,
							patindex('%[a-zA-Z]%',a),
							
							CASE WHEN LEN(a) > 0 and patindex('%[a-zA-Z]%',a) > 0 and patindex('%[a-zA-Z][ ][-+*/]%',a) > 0 THEN patindex('%[a-zA-Z][ ][-+*/]%',a)
								WHEN LEN(a) > 0 and patindex('%[a-zA-Z]%',a) > 0 and patindex('%[a-zA-Z][ ][-+*/]%',a) = 0 THEN LEN(a)
							ELSE 0 END - patindex('%[a-zA-Z]%',a)  ) as varchar(100))  as firstPart

			,cast(RIGHT(a,len(a) - 
							CASE WHEN LEN(a) > 0 and patindex('%[a-zA-Z]%',a) > 0 and patindex('%[a-zA-Z][ ][-+*/]%',a) > 0 THEN patindex('%[a-zA-Z][ ][-+*/]%',a)
								WHEN LEN(a) > 0 and patindex('%[a-zA-Z]%',a) > 0 and patindex('%[a-zA-Z][ ][-+*/]%',a) = 0 THEN LEN(a)
							ELSE 0 END ) as varchar(500)) AS remain
			
			,
				cast(
					
					case when patindex('%[a-zA-Z][ ][-+*/]%',a) > 0 then
									stuff(
											stuff(a,patindex('%[a-zA-Z]%',a),0,'[') 
											, patindex('%[a-zA-Z][ ][-+*/]%',a) + 2 
											,0,']'
										)	
						 else
							stuff(a,patindex('%[a-zA-Z]%',a),0,'[')  + ']'
						end
				as varchar(500)) AS finalText
				--cast(
				--	stuff(
				--			stuff(a,patindex('%[a-zA-Z]%',a),0,'[') 
				--		  , patindex('%[a-zA-Z][ ][-+*/]%',a) + 2 ,0 ,']')
				--as varchar(500)) AS finalText
    FROM cte
	
	union all

	select
		lvl +1  
		,word
		,cast(remain as varchar(500))		
		,patindex('%[a-zA-Z]%',remain) as startPrimul
		,CASE WHEN LEN(remain) > 0 and patindex('%[a-zA-Z]%',remain) > 0 and patindex('%[a-zA-Z][ ][-+*/]%',remain) > 0 THEN patindex('%[a-zA-Z][ ][-+*/]%',remain)
			 WHEN LEN(remain) > 0 and patindex('%[a-zA-Z]%',remain) > 0 and patindex('%[a-zA-Z][ ][-+*/]%',remain) = 0 THEN LEN(remain)
			 ELSE 0 END  as endPrimul 
		
		,endTotal +  patindex('%[a-zA-Z]%',remain) as startTotal
		,endTotal + CASE WHEN LEN(remain) > 0 and patindex('%[a-zA-Z]%',remain) > 0 and patindex('%[a-zA-Z][ ][-+*/]%',remain) > 0 THEN patindex('%[a-zA-Z][ ][-+*/]%',remain)
			 WHEN LEN(remain) > 0 and patindex('%[a-zA-Z]%',remain) > 0 and patindex('%[a-zA-Z][ ][-+*/]%',remain) = 0 THEN LEN(remain)
			 ELSE 0 END  as endTotal
		,cast(substring(remain,
							patindex('%[a-zA-Z]%',remain),
							
							CASE WHEN LEN(remain) > 0 and patindex('%[a-zA-Z]%',remain) > 0 and patindex('%[a-zA-Z][ ][-+*/]%',remain) > 0 THEN patindex('%[a-zA-Z][ ][-+*/]%',remain)
								WHEN LEN(remain) > 0 and patindex('%[a-zA-Z]%',remain) > 0 and patindex('%[a-zA-Z][ ][-+*/]%',remain) = 0 THEN LEN(remain)
							ELSE 0 END - patindex('%[a-zA-Z]%',remain)  ) as varchar(100)) as firstPart
		
		,cast(RIGHT(remain,len(remain) - 
							CASE WHEN LEN(remain) > 0 and patindex('%[a-zA-Z]%',remain) > 0 and patindex('%[a-zA-Z][ ][-+*/]%',remain) > 0 THEN patindex('%[a-zA-Z][ ][-+*/]%',remain)
								WHEN LEN(remain) > 0 and patindex('%[a-zA-Z]%',remain) > 0 and patindex('%[a-zA-Z][ ][-+*/]%',remain) = 0 THEN LEN(remain)
							ELSE 0 END ) as varchar(500)) as remain
		
		
		,CAST(
			case when len(finalText) > endTotal +  patindex('%[a-zA-Z]%',remain) 
					then stuff(finalText
							, endTotal +  patindex('%[a-zA-Z]%',remain)  + 2
							,0
							,'[')
				end
			+
			case when LEN(remain) > 0 and patindex('%[a-zA-Z]%',remain) > 0 and patindex('%[a-zA-Z][ ][-+*/]%',remain) = 0 
					--len(finalText) <= endTotal +  patindex('%[a-zA-Z]%',remain)
					then  ']'
				else
					']'
				end		
		 as varchar(500)) as finalText
		
	from reC
	where		
		patindex('%[a-zA-Z]%',remain)> 1
)
select
	a.finalText
from
(
	select 
		*
		,ROW_NUMBER() OVER(Partition by word Order by lvl desc) as rn
	from rec
	--order by
	--	word,lvl
)A
where
	a.rn = 1

output:

 finalText
100 / [Final Results]
[Pre Results] + 10 - [New Results]
[Premium] + 0.01
[Total Deduction] * 13
3

As I asked on the other forum, how many rows do you actually have? Just the 4 or ???