Turn character '10101010' into an int

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Turn character '10101010' into an int

Author

Topic

Teroman
Posting Yak Master

115 Posts

Posted - 2002-03-12 : 04:44:57

Hi,

I think the title says it all, i need to do bitwise ANDs on a number i get sent in a file, but for some reason the source sends it as a character string, not simply as the number.

So I need to turn it into an int first before I can get going

Thanks

Col

Teroman
Posting Yak Master

115 Posts

Posted - 2002-03-12 : 07:04:38

Ok, i have a way of doing this, but i need to check that my caracter field only contains 0's and 1's, it must be binary

Is there a simple way of doing this or do i have to check each character in turn, because i really dont want to have to do that.

thanks

col

Teroman
Posting Yak Master

115 Posts

Posted - 2002-03-12 : 07:25:31

all fixed now, found come ftuff about patindex and putting things in [ ] brackets

ToddV
Posting Yak Master

218 Posts

Posted - 2002-03-12 : 09:05:39

quote:

all fixed now, found come ftuff about patindex and putting things in [ ] brackets

How about this?

--I have a table called tally filled with a column called tally
--the table contains numbers 1-8000

Declare @String Varchar(20)
SET @String = '10101010'

SELECT SUM(Power(2,tally-1))
FROM Tally
WHERE Substring(Reverse(@String),tally,1) = 1

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-03-12 : 09:26:31

I think I'd be inclined to put a redundant constraint on the size of the tally value: AND tally <= LEN(@String). Otherwise it's going to try all 8000 rows: the optimizer won't know to stop when it gets to the end of the string.

Edited by - Arnold Fribble on 03/12/2002 09:32:12

ToddV
Posting Yak Master

218 Posts

Posted - 2002-03-12 : 09:38:42

quote:

I think I'd be inclined to put a redundant constraint on the size of the tally value: AND tally <= LEN(@String). Otherwise it's going to try all 8000 rows: the optimizer won't know to stop when it gets to the end of the string.

Edited by - Arnold Fribble on 03/12/2002 09:32:12

Agreed

davidpardoe
Constraint Violating Yak Guru

324 Posts

Posted - 2002-03-12 : 10:51:11

Here is a UDF to do this (no error checking on the input string)...

CREATE FUNCTION BinToInt
   (@BinString varchar(100) )
RETURNS int

AS
BEGIN
declare @trans_value int
set @trans_value=0
declare @count int,@maxcount int
set @maxcount=len(@BinString)
set @count=1
while @count<=@maxcount
	begin
	set @trans_value=@trans_value+substring(reverse(@BinString),@count,1)*power(2,@count-1)
	set @count=@count+1
	end
return @trans_value
END

============
The Dabbler!

davidpardoe
Constraint Violating Yak Guru

324 Posts

Posted - 2002-03-12 : 10:58:24

You could use something like the following to check if "YourString" contains just 1's and 0's. It replaces all the 1's and 0's with X's and then compares this with a string of X's as long as "YourString"...

select case when replicate('X',len(YourString))=replace(replace(YourString,'1','X'),'1','X') then 'OK' else 'NotOK' end

============
The Dabbler!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources