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.
Author |
Topic |
chiragvm
Yak Posting Veteran
65 Posts |
Posted - 2013-08-21 : 02:18:42
|
hi to alli 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 12 13 14 05 06 07 1upper 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);-- SwePesoSELECT s.RowID, s.Data, 2 * v.Number + f.Part AS BitPosition, f.SectionFROM @Sample AS sINNER JOIN master.dbo.spt_values AS v ON v.Type = 'P' AND v.Number BETWEEN 0 AND DATALENGTH(s.Data) - 1CROSS 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 |
|
|
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);-- SwePesoSELECT s.RowID, s.Data, 2 * v.Number + f.Part AS BitPosition, f.SectionFROM @Sample AS sINNER JOIN master.dbo.spt_values AS v ON v.Type = 'P' AND v.Number BETWEEN 0 AND DATALENGTH(s.Data) - 1CROSS 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 |
|
|
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. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-08-21 : 14:45:50
|
[code]-- Onetime operationCREATE TABLE dbo.Numbers ( Number INT PRIMARY KEY CLUSTERED );INSERT dbo.Numbers ( Number )SELECT 1 + 250 * v.Number + w.NumberFROM master.dbo.spt_values AS vINNER JOIN master.dbo.spt_values AS w ON w.Type = 'P' AND w.Number BETWEEN 0 AND 249WHERE v.Type = 'P' AND v.Number BETWEEN 0 AND 249ORDER BY 1 + 250 * v.Number + w.Number;-- SwePesoSELECT s.RowID, s.Data, 2 * v.Number + f.Part AS BitPosition, f.SectionFROM @Sample AS sINNER JOIN dbo.Numbers AS v ON v.Number BETWEEN 0 AND DATALENGTH(s.Data) - 1CROSS 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 |
|
|
|
|
|
|
|