14,699,325 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.