Click here to Skip to main content
14,699,325 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
i have denominations stored in a database(sql server 2008) column seperated by $ sign..like 1000*2=2000$500*1=500$100*0=0$50*0=0$20*0=0 i want to split this string and display in gridview column like
1000*2=2000
500*1=500
100*0=0
50*0=0
20*0=0


please help me

Thanks in advance
Posted

Hello,
Check out following link.

sql-server-user-defined-funtion-to-split-the-string-by-special-char/[^]

Hope this help you
   
You can use Common Table Expressions[^]:
DECLARE @tmp TABLE (s VARCHAR(300))

INSERT INTO @tmp (s)
SELECT '1000*2=2000$500*1=500$100*0=0$50*0=0$20*0=0'

;WITH CTE AS
(
	--initial
	SELECT LEFT(s, CHARINDEX('$',s)-1) AS [Value], RIGHT(s, LEN(s)- CHARINDEX('$',s)) AS [Remainder]
	FROM @tmp
	WHERE CHARINDEX('$',s)>0
	UNION ALL
	SELECT LEFT([Remainder], CHARINDEX('$',[Remainder])-1) AS [Value], RIGHT([Remainder], LEN([Remainder])- CHARINDEX('$',[Remainder])) AS [Remainder]
	FROM CTE
	WHERE CHARINDEX('$',[Remainder])>0
	UNION ALL
	SELECT [Remainder] AS [Value], NULL AS [Remainder]
	FROM CTE
	WHERE CHARINDEX('$',[Remainder])=0
)
SELECT *
FROM CTE


Result:
[Value]     [Remainder]
1000*2=2000 500*1=500$100*0=0$50*0=0$20*0=0
500*1=500   100*0=0$50*0=0$20*0=0
100*0=0     50*0=0$20*0=0
50*0=0      20*0=0
20*0=0      NULL
   
Declare @Calculation varchar(200)
SET @Calculation= '1000*2=2000$500*1=500$100*0=0$50*0=0$20*0=0'
Declare @CalculationTemp varchar(200)
SET @CalculationTemp= null

WHILE LEN(@Calculation)>0
BEGIN
IF PATINDEX('%$%',@Calculation) > 0
BEGIN
Set @CalculationTemp=SUBSTRING(@Calculation,0,PATINDEX('%$%',@Calculation))
SET @Calculation = SUBSTRING(@Calculation, LEN(@CalculationTemp + '|') + 1, LEN(@Calculation))
PRINT @CalculationTemp
END
ELSE
BEGIN
SET @CalculationTemp = @Calculation
SET @Calculation = NULL
END
END
   
One solution could be to replace $ sign with Carriage Return "\n" at query level and return result set to grid. The grid should automatically render it your way because of the carriage return.

I have not tested it but it should work.
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900