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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

chiragvm
Yak Posting Veteran

India
65 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
30421 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
65 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

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