January 9, 2012 at 9:12 am
Is it too late to say Happy New Year? I want to say Happy New Year to every one in my first post in the new year.
I have a question here and couldn't find answer so far, hope to get some clue here:
I have a string variable @str1 length of 5, record is like 10101, 01011...
This @str1 needs to be bitwise calculated with a boolean parameter.
Is there any simple way I can do this? although I can do this in front end coding.
Thanks in advance.
January 9, 2012 at 9:43 am
ugg; except for building a presentation string for binary,
i always leave my values as integers for data manipulation...very easy to test each column for whatever it represents them.
any chance you can get the data as integers instead of mapped out as a string?
DECLARE @val int
SET @val = 42
SELECT
'Flag Representing [2^0]',CASE @val & POWER(2,0) WHEN 0 THEN 'True' ELSE 'False' END ,
'Flag Representing [2^1]',CASE @val & POWER(2,1) WHEN 1 THEN 'True' ELSE 'False' END ,
'Flag Representing [2^2]',CASE @val & POWER(2,2) WHEN 2 THEN 'True' ELSE 'False' END ,
'Flag Representing [2^3]',CASE @val & POWER(2,3) WHEN 3 THEN 'True' ELSE 'False' END ,
'Flag Representing [2^4]',CASE @val & POWER(2,4) WHEN 4 THEN 'True' ELSE 'False' END ,
'Flag Representing [2^5]',CASE @val & POWER(2,5) WHEN 5 THEN 'True' ELSE 'False' END ,
'Flag Representing [2^6]',CASE @val & POWER(2,6) WHEN 6 THEN 'True' ELSE 'False' END ,
'Flag Representing [2^7]',CASE @val & POWER(2,7) WHEN 7 THEN 'True' ELSE 'False' END ,
'Flag Representing [2^8]',CASE @val & POWER(2,8) WHEN 8 THEN 'True' ELSE 'False' END ,
'Flag Representing [2^9]',CASE @val & POWER(2,9) WHEN 9 THEN 'True' ELSE 'False' END ,
'Flag Representing [2^10]',CASE @val & POWER(2,10) WHEN 10 THEN 'True' ELSE 'False' END ,
'Flag Representing [2^11]',CASE @val & POWER(2,11) WHEN 11 THEN 'True' ELSE 'False' END ,
'Flag Representing [2^12]',CASE @val & POWER(2,12) WHEN 12 THEN 'True' ELSE 'False' END ,
'Flag Representing [2^13]',CASE @val & POWER(2,13) WHEN 13 THEN 'True' ELSE 'False' END ,
'Flag Representing [2^14]',CASE @val & POWER(2,14) WHEN 14 THEN 'True' ELSE 'False' END ,
'Flag Representing [2^15]',CASE @val & POWER(2,15) WHEN 15 THEN 'True' ELSE 'False' END ,
'Flag Representing [2^16]',CASE @val & POWER(2,16) WHEN 16 THEN 'True' ELSE 'False' END ,
'Flag Representing [2^17]',CASE @val & POWER(2,17) WHEN 17 THEN 'True' ELSE 'False' END ,
'Flag Representing [2^18]',CASE @val & POWER(2,18) WHEN 18 THEN 'True' ELSE 'False' END ,
'Flag Representing [2^19]',CASE @val & POWER(2,19) WHEN 19 THEN 'True' ELSE 'False' END ,
'Flag Representing [2^20]',CASE @val & POWER(2,20) WHEN 20 THEN 'True' ELSE 'False' END ,
'Flag Representing [2^21]',CASE @val & POWER(2,21) WHEN 21 THEN 'True' ELSE 'False' END ,
'Flag Representing [2^22]',CASE @val & POWER(2,22) WHEN 22 THEN 'True' ELSE 'False' END ,
'Flag Representing [2^23]',CASE @val & POWER(2,23) WHEN 23 THEN 'True' ELSE 'False' END ,
'Flag Representing [2^24]',CASE @val & POWER(2,24) WHEN 24 THEN 'True' ELSE 'False' END ,
'Flag Representing [2^25]',CASE @val & POWER(2,25) WHEN 25 THEN 'True' ELSE 'False' END ,
'Flag Representing [2^26]',CASE @val & POWER(2,26) WHEN 26 THEN 'True' ELSE 'False' END ,
'Flag Representing [2^27]',CASE @val & POWER(2,27) WHEN 27 THEN 'True' ELSE 'False' END ,
'Flag Representing [2^28]',CASE @val & POWER(2,28) WHEN 28 THEN 'True' ELSE 'False' END ,
'Flag Representing [2^29]',CASE @val & POWER(2,29) WHEN 29 THEN 'True' ELSE 'False' END ,
'Flag Representing [2^30]',CASE @val & POWER(2,30) WHEN 30 THEN 'True' ELSE 'False' END
Lowell
January 9, 2012 at 10:13 am
representing the data with int is probably not as clear as current varchar:
Each digit represents a record in another table, let me explain here so you can see if my design is good, your suggestion could be better though:
Table 1: has a field called categories with data like 10101, each digit corresponds yes or no to records in table2 with data like:
1 Cat1
2 Cat2
...
5 Cat5
So, looking at record1(10101) in table 1 I know this record has Cat1,3 and 5 while no Cat 2 and 4.
When user wants to tag the record to a different Categories, I have a checkbox list for them to select from, in front end, I will read each checkbox's checked status, i.e. 1 or 0, and then XOR with 10101 and get the new category setting.
Is my approach a good one? or what else can I do to make it easier and clearer?
Thank you for the input.
January 9, 2012 at 10:14 am
this was interesting...
not sure if you want to get the data as rows or columns, you might need to pivot this data if you need it as columns, see what this does for you:
;with myStrings (val)
AS (
SELECT '10101' UNION ALL
SELECT '1010100' UNION ALL
SELECT '010111111' )
,
FormattedStrings AS (
SELECT right('0000000000000000000000000000000' + val ,31) As sval
FROM myStrings),
MiniTally AS (
SELECT TOP 31 row_number() OVER (order by name) As N from sys.columns order by name )
select
sval,
MiniTally.N,
CONVERT(int,SUBSTRING(REVERSE(sval),MiniTally.N,1)) As bval,
POWER(2,N-1) As thePower,
CASE
WHEN (CONVERT(int,SUBSTRING(REVERSE(sval),MiniTally.N,1)) & 1) = 0
THEN 'False'
ELSE 'True'
END As [True?]
FROM FormattedStrings
CROSS JOIN MiniTally
order by sval,MiniTally.N
Lowell
January 9, 2012 at 10:55 am
I appreciate your help but honorsly I don't understand your query and I am not sure if you understand my question (sorry for saying that)
Let's say my table1 has this records:
;with table1 (id, val)
AS (
SELECT 1, '10101' UNION ALL
SELECT 2, '10100' UNION ALL
SELECT 3, '01011' )
I need a stored procedure or function with @id, @parameter to do this:
if @id = 1 update record1 in table1 from 10101 to @parameter XOR 10101
if @id = 2 update record2 in table1 from 10100 to @parameter XOR 10100
if @id = 3 update record3 in table1 from 01011 to @parameter XOR 01011
I hope this is clear now.
Thanks again for your time
January 9, 2012 at 2:26 pm
I did it, but I don't like my code at all, just too ugly, anyway, I post here in case any one need it.
ALTER function [dbo].[fnGetNewCategoryByDocID] (@DocID int, @bitpos int, @Param bit) returns varchar(5) AS
begin
--@DocID: the doc id
--@bitpos: the position of the category to be returned
--@Param: the value of the new category
declare @oldstr varchar(5)
select @oldstr = Category from document where ID = @DocID
declare @newstr varchar(5)
declare @thebit bit
declare @newbit varchar(1)
select @thebit = substring(@oldstr, @bitpos, 1)
select @newbit = @thebit & @param
select @newstr =
case
when @bitpos = 1 then
convert(varchar(1), @newbit) + substring(@oldstr, 2, 4)
when @bitpos = 2 then
substring(@oldstr, 1, 1) + convert(varchar(1), @newbit) + substring(@oldstr, 3, 3)
when @bitpos = 3 then
substring(@oldstr, 1, 2) + convert(varchar(1), @newbit) + substring(@oldstr, 4, 2)
when @bitpos = 4 then
substring(@oldstr, 1, 3) + convert(varchar(1), @newbit) + substring(@oldstr, 5, 1)
else
substring(@oldstr, 1, 4) + convert(varchar(1), @newbit)
end
return @newstr
end
Thanks for the reply.
January 9, 2012 at 2:53 pm
halifaxdal (1/9/2012)
Each digit represents a record in another table, let me explain here so you can see if my design is good, your suggestion could be better though:Table 1: has a field called categories with data like 10101, each digit corresponds yes or no to records in table2 with data like:
1 Cat1
2 Cat2
...
5 Cat5
I think that you're complicating things by representing the state of another table in this field. The other table has all of the information you need about it's own state, you shouldn't have to use this field to store and manipulate that state.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply