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)
 Binary to table

Author  Topic 

chiragvm
Yak Posting Veteran

65 Posts

Posted - 2013-08-21 : 02:18:42
hi to all

i have a table with 2 column srno int and present varbinary
in present column all value has 0x010101000001101....1 (96 character o or 1 )

i want to all 96 value as a column for ex

present
----------------------
0x1110001111000000...


need data in to 2 column like

No Present
-----------------------
1 1
2 1
3 1
4 0
5 0
6 0
7 1

upper table represent a single bit of varbinary field

-------------
Chirag
India
Sr. Sw.Engineer

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-21 : 02:44:58
[code]DECLARE @Sample TABLE
(
RowID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
Data VARBINARY(100) NOT NULL
);

INSERT @Sample
(
Data
)
VALUES (0x010101000001101);

-- SwePeso
SELECT s.RowID,
s.Data,
2 * v.Number + f.Part AS BitPosition,
f.Section
FROM @Sample AS s
INNER JOIN master.dbo.spt_values AS v ON v.Type = 'P'
AND v.Number BETWEEN 0 AND DATALENGTH(s.Data) - 1
CROSS APPLY (
VALUES (0, SUBSTRING(s.Data, v.Number + 1, 1) / 16),
(1, SUBSTRING(s.Data, v.Number + 1, 1) % 16)
) AS f(Part, Section)
ORDER BY s.RowID,
v.Number,
f.Part;[/code]


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

chiragvm
Yak Posting Veteran

65 Posts

Posted - 2013-08-21 : 10:12:06
Hello SwePeso

the above query result work perfect but up to 4096 row but we have a binary data length is up to 40000 can you suggest some other way or any changes.


quote:
Originally posted by SwePeso

DECLARE	@Sample TABLE
(
RowID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
Data VARBINARY(100) NOT NULL
);

INSERT @Sample
(
Data
)
VALUES (0x010101000001101);

-- SwePeso
SELECT s.RowID,
s.Data,
2 * v.Number + f.Part AS BitPosition,
f.Section
FROM @Sample AS s
INNER JOIN master.dbo.spt_values AS v ON v.Type = 'P'
AND v.Number BETWEEN 0 AND DATALENGTH(s.Data) - 1
CROSS APPLY (
VALUES (0, SUBSTRING(s.Data, v.Number + 1, 1) / 16),
(1, SUBSTRING(s.Data, v.Number + 1, 1) % 16)
) AS f(Part, Section)
ORDER BY s.RowID,
v.Number,
f.Part;



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



-------------
Chirag
India
Sr. Sw.Engineer
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-08-21 : 11:05:06
You might try changing the data type from VARBINARY(100) to VARBIANRY(MAX) and see if that works. I don't remember off the top of my head if all those functions work on (MAX) data types, but I think they do.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-21 : 14:45:50
[code]-- Onetime operation
CREATE TABLE dbo.Numbers
(
Number INT PRIMARY KEY CLUSTERED
);

INSERT dbo.Numbers
(
Number
)
SELECT 1 + 250 * v.Number + w.Number
FROM master.dbo.spt_values AS v
INNER JOIN master.dbo.spt_values AS w ON w.Type = 'P'
AND w.Number BETWEEN 0 AND 249
WHERE v.Type = 'P'
AND v.Number BETWEEN 0 AND 249
ORDER BY 1 + 250 * v.Number + w.Number;

-- SwePeso
SELECT s.RowID,
s.Data,
2 * v.Number + f.Part AS BitPosition,
f.Section
FROM @Sample AS s
INNER JOIN dbo.Numbers AS v ON v.Number BETWEEN 0 AND DATALENGTH(s.Data) - 1
CROSS APPLY (
VALUES (0, SUBSTRING(s.Data, v.Number + 1, 1) / 16),
(1, SUBSTRING(s.Data, v.Number + 1, 1) % 16)
) AS f(Part, Section)
ORDER BY s.RowID,
v.Number,
f.Part;[/code]


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page
   

- Advertisement -