BinToBit function

Anyone happen to have a function that does the following:

I have an integer for example 57.

This translates to binary: 111001

I'm looking for a function like this:

Code Snippet

CREATE FUNCTION [dbo].[BinToBit] (@.ValueCol int, @.Number TinyInt)

RETURNS bit AS

If I would call the function:

select dbo.BinToBit(57, 0) it should return 1

select dbo.BinToBit(57, 1) it should return 0

select dbo.BinToBit(57, 2) it should return 0

select dbo.BinToBit(57, 3) it should return 1

select dbo.BinToBit(57, 4) it should return 1

select dbo.BinToBit(57, 5) it should return 1

I've been looking on the net, because I'm convinced someone must have this kind of function, unfortunately haven't been able to find it.

CREATE FUNCTION [dbo].[BinToBit] (@.ValueCol int, @.Number TinyInt)

RETURNS bit AS

BEGIN

RETURN (@.ValueCol & POWER(2,@.Number))

END

|||Ha! Perfect thanks!

BINARY_CHECKSUM algorithm

Hello,

Do you know if the algorithm for the BINARY_CHECKSUM function in documented
somewhere?
I would like to use it to avoid returning some string fields from the
server.
By returning only the checksum I could lookup the string in a hashtable and
I think this could make the code more efficient on slow connections.

Thanks in advanced and kind regards,

Orly JuniorI don't know where the algorithm is documented but I don't think it will
help you. There are many more possible strings than checksums so there isn't
a one-to-one correspondence between them. Unless your set of possible
strings is constrained to a small set you couldn't guarantee to translate a
checksum back into a string and you'd still have to maintain a lookup table
of strings on the client side. So if your set of strings is small and
constrained then you may as well invent your own codes. Alternatively, take
a look at Huffman Coding or one of the zip compression algorithms.

--
David Portas
SQL Server MVP
--|||Orly Junior (nomail@.nomail.com) writes:
> Do you know if the algorithm for the BINARY_CHECKSUM function in
> documented somewhere? I would like to use it to avoid returning some
> string fields from the server. By returning only the checksum I could
> lookup the string in a hashtable and I think this could make the code
> more efficient on slow connections.

Risky business. The checksum algorithm is fairly simple-minded. I beleive
it uses some xor mechanism. I don't have the references around right now,
but I recall that SQL Server MVP Steve Kass demonstrated how some quite
small changes could result in the same checksum.

Better in such case, to augment the table with a timestamp column. Such
a column is automatically updated every time SQL Server updates the
row. So you could store the timestamp client side, and pass that value,
if the table has the same value, there is no need for a refresh.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Orly,

Here is what I believe it does for a single varchar
column. It's not a particularly good hash function at
all.

create function binary_checksum_varchar (
@.t varchar(1000)
) returns int as begin
declare @.b bigint set @.b = 0
declare @.c tinyint
declare @.s bit set @.s = 0
declare @.i int set @.i = 1

while @.i <= len(@.t) begin
set @.c = ascii(substring(@.t,@.i,1))
set @.b = @.b / 16 * 16 + @.b % 16 ^ @.c / 16
set @.b = @.b * 16 + @.c % 16
if @.c >= 128 begin
set @.b = @.b ^ 0xFF
set @.s = 1 - @.s
end
set @.b = @.b % 0x0100000000 ^ @.b / 0x0100000000
set @.i = @.i + 1
end

if @.s = 1 set @.b = @.b ^ 0xFFFFFFFF
if @.b >= 0x80000000 set @.b = @.b | 0xFFFFFFFF00000000
return @.b
end
go

You'll find more information if some of the threads
here:

http://groups.google.co.uk/groups?q...+kass+sqlserver

Steve Kass
Drew University

Orly Junior wrote:

> Hello,
> Do you know if the algorithm for the BINARY_CHECKSUM function in documented
> somewhere?
> I would like to use it to avoid returning some string fields from the
> server.
> By returning only the checksum I could lookup the string in a hashtable and
> I think this could make the code more efficient on slow connections.
> Thanks in advanced and kind regards,
> Orly Junior
>

Big Challange

hi all

i need to write a function to replace one field from another field in strode procedure .

as a example

SELECT
profc.SURNAME + ', ' + profc.FORENAME AS ProfCarer,
SPECS.DESCRIPTION AS Speciality,
steams.STEAM_REFNO_NAME AS StaffTeam,
REFS.SORRF_REFNO_DESCRIPTION AS SourceOfReferral,
COUNT(REFS.REFRL_REFNO) AS NoOfOpenReferrals
FROM REFS
LEFT OUTER JOIN PROFCARERS profc ON REFS.REFTO_PROCA_REFNO = profc.PROCA_REFNO
LEFT OUTER JOIN SPECS ON REFS.REFTO_SPECT_REFNO = SPECS.SPECT_REFNO
LEFT OUTER JOIN STAFFTEAMS steams ON REFS.REFTO_STEAM_REFNO = steams.STEAM_REFNO
INNER JOIN PrimaryCareTrust pct ON REFS.PCT_CODE = pct.PCTCode --AND REFS.CLOSR_DATE IS NULL

i need to replace REFS.PCT_CODE FROM GEOGAREA.PCT_CODE THIS FUNCTION NEED ONE INPUT PARAMETER CALLED REFS.REFTOPROCA_REFNO

Any Idea
Thank's

I'm a little confused.

You want to translate the REFS.PCT_CODE in order to use it in the joing on PrimaryCareTrust?

|||ya i need to replace refs.pct_code from another cord in all the strode procs|||

OK, what is the the code you want as the replacement?
Where does it come from?

Where does it go within the query?

Please provide more info.

|||the code is GEOGAREA.PCT_CODE its from GeographicArea table
it just replase that REFS.PCT_CODE
|||

This doesn't make sense, but anyway:

Code Snippet

SELECT

profc.SURNAME +', '+ profc.FORENAME AS ProfCarer,

SPECS.DESCRIPTION AS Speciality,

steams.STEAM_REFNO_NAME AS StaffTeam,

REFS.SORRF_REFNO_DESCRIPTION AS SourceOfReferral,

COUNT(REFS.REFRL_REFNO)AS NoOfOpenReferrals

FROM REFS

INNERJOIN GEOGAREA

ON REFS.PCT_CODE = GEOGAREA.PCT_CODE

LEFTOUTERJOIN PROFCARERS profc ON REFS.REFTO_PROCA_REFNO = profc.PROCA_REFNO

LEFTOUTERJOIN SPECS ON REFS.REFTO_SPECT_REFNO = SPECS.SPECT_REFNO

LEFTOUTERJOIN STAFFTEAMS steams ON REFS.REFTO_STEAM_REFNO = steams.STEAM_REFNO

INNERJOIN PrimaryCareTrust pct ON GEOGAREA.PCT_CODE = pct.PCTCode --AND REFS.CLOSR_DATE IS NULL

|||ya i know this mate. i have lots of procedures so what i want to do is i need create a function that i execute that it'll go and search that REFS.PCT_CODE in each and every procedures and replace it by GEOGAREA.PCT_CODE

can we do this?|||

You bet.

REFS.PCT_CODE is the input

GEOGAREA.PCT_CODE is the output

Now, how are REFS and GEOGAREA related?
How does REFS.PCT_CODE find the correct entry in GEOGAREA?

|||

Spend a little bit time to change your quires(even it is on multiple stored procedures).

If you use function it may decrease the performance.

|||PCT_CODE STANDS geographic area code this are same but refs.pct_code is going to be change thats why.|||

Niranga,

Please provide some details and specifics.

What is the current DDL/schema?

How is it changing?

Provide some sample table data and your expected results.