quote:

---

Originally posted by visakh16

why are storing values like this?
have you heard about normalisation and 1 st normal form?
storing like this will really complicate matters

in sql 2000 there's no easy way of doing this other than looping logic

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---

quote:

---

Originally posted by SwePeso

How many split values is there at most in any single column?

---

N 56°04'39.26"
E 12°55'05.63"

---

quote:

---

Originally posted by visakh16

in sql 2000 there's no easy way of doing this other than looping logic

---

DECLARE	@Sample TABLE
	(
		InvID INT,
		ReceiptNo VARCHAR(1000),
		Amount VARCHAR(1000),
		[Date] DATETIME
	)

SET DATEFORMAT DMY

INSERT	@Sample
SELECT	1, '00001/00002', '25,000/34,000', '12-11-2011' UNION ALL
SELECT	2, NULL, NULL, NULL UNION ALL
SELECT	3, '00003', '24,000', '13-11-2011' UNION ALL
SELECT	4, '00004/00005/00006', '12,231/21,211/555', '14-11-2011'

-- SwePeso
SELECT	InvID,
	ReceiptNo,
	Amount,
	[Date]
FROM	(
		SELECT	InvID,
			PARSENAME(REPLACE(ReceiptNo, '/', '.'), 4) AS ReceiptNo,
			PARSENAME(REPLACE(Amount, '/', '.'), 4) AS Amount,
			[Date]
		FROM	@Sample

		UNION

		SELECT	InvID,
			PARSENAME(REPLACE(ReceiptNo, '/', '.'), 3) AS ReceiptNo,
			PARSENAME(REPLACE(Amount, '/', '.'), 3) AS Amount,
			[Date]
		FROM	@Sample

		UNION

		SELECT	InvID,
			PARSENAME(REPLACE(ReceiptNo, '/', '.'), 2) AS ReceiptNo,
			PARSENAME(REPLACE(Amount, '/', '.'), 2) AS Amount,
			[Date]
		FROM	@Sample

		UNION

		SELECT	InvID,
			PARSENAME(REPLACE(ReceiptNo, '/', '.'), 1) AS ReceiptNo,
			PARSENAME(REPLACE(Amount, '/', '.'), 1) AS Amount,
			[Date]
		FROM	@Sample
	) AS d
WHERE	ReceiptNo IS NOT NULL AND Amount IS NOT NULL AND [Date] IS NOT NULL
	OR ReceiptNo IS NULL AND Amount IS NULL AND [Date] IS NULL