DECLARE @Foo AS TABLE (Val BIGINT)

INSERT @Foo (Val)
VALUES
(0), 
(1),
(2), -- Second Right
(64), -- Sixth Right
(66),  -- Second And Sixth Right
(1569918) -- also Second And Sixth Right



SELECT 
	Val,
	Val & POWER(2, 1) AS SecondRight,
	Val & POWER(2, 6) AS SixthRight,
	CASE 
		WHEN 
			Val & POWER(2, 1)  > 0 
			AND Val & POWER(2, 6) > 0
			THEN 1 
		ELSE 0 
	END AS IsSecondAndSixth
FROm @Foo

quote:

---

Originally posted by awilcox1014

While I have a great deal of experience with DB2 - I am fairly new to SQL Server. I am working on a project right now trying to help the tester write some SQL QA testing. I just came across a requirement that says that we are to convert the OPTIONS column to binary and if the second right most digit is 0 and the 6th right most digit is 0 then count the record as 1. Otherwise is 0.

Nears as I can tell there is no EASY way to do this. So I am out of my league trying to help here. How do I convert the OPTIONS column - which is defined as a BIGINT so I can do this conversion? I wish the ETL people had just applied a Y/N to the record based on this rule?

As a side - is this common in SQL Server? This query is running inside of a dashboard. Seems like this is an inefficient way to do this to me. SOmeone please explain if you can.

THANKS!!!

---

quote:

---

Originally posted by awilcox1014

OK - Sorry - I had no sample when I started. So I used something similar to the post by Sanjep and created a function to the the conversion - this works great on my local instance. I do not have the authority to create in prod though. Will have to find a DBA if I go that route. To clarify - I am to convert to binary - if the second digit from the right is a 0 and the 6th most right digit is a 0 then I am to add a 1 to the count column, else 0. Is this possible without creating the function (which works great).

---