help query for multivalue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

help query for multivalue

Author

Topic

kabon
Starting Member

48 Posts

Posted - 2013-09-17 : 22:08:41

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

549 Posts

Posted - 2013-09-17 : 22:41:21

Here is a way to do it:
[CODE]

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');
[/CODE]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-09-17 : 22:48:28

is it always has 4 values or it may varies ?

KH
[spoiler]Time is always against us[/spoiler]

kabon
Starting Member

48 Posts

Posted - 2013-09-17 : 23:48:39

no, it can more than 4 values.

kabon
Starting Member

48 Posts

Posted - 2013-09-18 : 03:08:52

no, it can more than 4 values.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-09-18 : 04:15:18

[code]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);[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources