Click here to Skip to main content
14,668,708 members
Rate this:
Please Sign up or sign in to vote.
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

Rate this:
Please Sign up or sign in to vote.

Solution 2

Hello,
Check out following link.

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

Hope this help you
   
Rate this:
Please Sign up or sign in to vote.

Solution 3

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
   
Rate this:
Please Sign up or sign in to vote.

Solution 4

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
   
Rate this:
Please Sign up or sign in to vote.

Solution 1

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, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100