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)
 Binary to table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

chiragvm
Yak Posting Veteran

India
62 Posts

Posted - 08/21/2013 :  02:18:42  Show Profile  Reply with Quote
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

Sweden
30114 Posts

Posted - 08/21/2013 :  02:44:58  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

chiragvm
Yak Posting Veteran

India
62 Posts

Posted - 08/21/2013 :  10:12:06  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4612 Posts

Posted - 08/21/2013 :  11:05:06  Show Profile  Reply with Quote
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

Sweden
30114 Posts

Posted - 08/21/2013 :  14:45:50  Show Profile  Visit SwePeso's Homepage  Reply with Quote
-- 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;



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.09 seconds. Powered By: Snitz Forums 2000