14,693,113 members
See more:
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

Posted

## Solution 2

Hello,

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

## 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```

## 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

## 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.

Top Experts
Last 24hrsThis month
 CPallini 225 Richard Deeming 195 OriginalGriff 180 Richard MacCutchan 115 KarstenK 90
 OriginalGriff 5,604 Richard Deeming 3,065 Richard MacCutchan 2,569 CPallini 1,932 Patrice T 1,340

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