SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 help query for multivalue
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kabon
Starting Member

Indonesia
48 Posts

Posted - 09/17/2013 :  22:08:41  Show Profile  Reply with Quote
I have data like this in table A.Column 3 :

20569980.00::378845.00::20758526.00::190286.00

I want to make that data into like this:

0000020569980.00::0000000378845.00::0000020758526.00::0000000190286.00

Do you have idea to solve this problem?

MuMu88
Aged Yak Warrior

547 Posts

Posted - 09/17/2013 :  22:41:21  Show Profile  Reply with Quote
Here is a way to do it:


DECLARE @String VARCHAR(120) = '20569980.00::378845.00::20758526.00::190286.00';

-- OUTPUT: 0000020569980.00::0000000378845.00::0000020758526.00::0000000190286.00

SELECT STUFF(STUFF(STUFF(STUFF(@String,38,0, '0000000'), 25, 0, '00000'), 14, 0, '0000000'), 1, 0, '00000');

Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17650 Posts

Posted - 09/17/2013 :  22:48:28  Show Profile  Reply with Quote
is it always has 4 values or it may varies ?


KH
Time is always against us

Go to Top of Page

kabon
Starting Member

Indonesia
48 Posts

Posted - 09/17/2013 :  23:48:39  Show Profile  Reply with Quote
no, it can more than 4 values.
Go to Top of Page

kabon
Starting Member

Indonesia
48 Posts

Posted - 09/18/2013 :  03:08:52  Show Profile  Reply with Quote
no, it can more than 4 values.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 09/18/2013 :  04:15:18  Show Profile  Visit SwePeso's Homepage  Reply with Quote
DECLARE	@Sample TABLE
	(
		Data VARCHAR(100) NOT NULL
	);

INSERT	@Sample
	(
		Data
	)
VALUES	('20569980.00::378845.00::20758526.00::190286.00');

-- SwePeso
WITH cteSource(Data)
AS (
	SELECT		f.Data
	FROM		@Sample AS s
	CROSS APPLY	(
				VALUES	(CAST('<i>' + REPLACE(s.Data, '::', '</i><i>') + '</i>' AS XML))
			) AS f(Data)
)
SELECT	STUFF(Data, 1, 2, '') AS Data
FROM	(
		SELECT		'::' + RIGHT('0000000000000000' + n.value('(.)', 'VARCHAR(100)'), 16)
		FROM		cteSource AS s
		CROSS APPLY	s.Data.nodes('(i)') AS d(n)
		FOR XML		PATH('')
	) AS d(Data);



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000