quote:

---

Originally posted by setbasedisthetruepath

First, you should consider replacing the char(16) with binary(2).

You're askig for a procedure that returns all rows where the (input parameter AND column value) == column value. If you do this w/ a binary column you can write it just as above.

Jonathan
Gaming will never be the same

---

CREATE TABLE A (A char(16))
GO

INSERT INTO A(A)
SELECT '0000000010001000' UNION ALL
SELECT '1000100011001000' UNION ALL
SELECT '0000000001000010' UNION ALL
SELECT '0000000011001000' UNION ALL
SELECT '1000100011001000' UNION ALL
SELECT '0000000011100010' UNION ALL
SELECT '0000000010001000' UNION ALL
SELECT '1000100111001000' UNION ALL
SELECT '0000000001000010' UNION ALL
SELECT '0000001010001100' UNION ALL
SELECT '1000100011001000' UNION ALL
SELECT '0000010001000010' UNION ALL
SELECT '0000000010001000' UNION ALL
SELECT '1000100011001000' UNION ALL
SELECT '0000100001000010'
GO

DECLARE @a char(16), @x int, @y int, @z int,  @SQL varchar(8000)
SELECT @a = '0000000010001000', @SQL = 'SELECT * FROM A WHERE', @z = 0, @y = 0
SELECT @x = LEN(REPLACE(@a,'0',''))

WHILE @y < @x
	BEGIN
		SELECT @z = CHARINDEX('1',@A,@z+1)
		SELECT @SQL = @SQL + ' SUBSTRING(A,'+CONVERT(varchar(4),@z)+',1) = ' 
			+ ''''+ '1' + '''' + ' AND'
			, @y = @y + 1
	END
SELECT @SQL = LEFT(@SQL,LEN(@SQL)-4)
EXEC(@SQL)

DROP TABLE A
GO

quote:

---

Originally posted by tduggan

CONVERT it to BINARY in the statement.

SELECT CONVERT(BINARY, SomeColumn)
FROM SomeTable

Tara

---

quote:

---

Originally posted by X002548

Well...I didn't think (damn it again) about that...

So I wasted my time with a sledge hammer

CREATE TABLE A (A char(16))
GO

INSERT INTO A(A)
SELECT '0000000010001000' UNION ALL
SELECT '1000100011001000' UNION ALL
SELECT '0000000001000010' UNION ALL
SELECT '0000000011001000' UNION ALL
SELECT '1000100011001000' UNION ALL
SELECT '0000000011100010' UNION ALL
SELECT '0000000010001000' UNION ALL
SELECT '1000100111001000' UNION ALL
SELECT '0000000001000010' UNION ALL
SELECT '0000001010001100' UNION ALL
SELECT '1000100011001000' UNION ALL
SELECT '0000010001000010' UNION ALL
SELECT '0000000010001000' UNION ALL
SELECT '1000100011001000' UNION ALL
SELECT '0000100001000010'
GO

DECLARE @a char(16), @x int, @y int, @z int,  @SQL varchar(8000)
SELECT @a = '0000000010001000', @SQL = 'SELECT * FROM A WHERE', @z = 0, @y = 0
SELECT @x = LEN(REPLACE(@a,'0',''))

WHILE @y < @x
	BEGIN
		SELECT @z = CHARINDEX('1',@A,@z+1)
		SELECT @SQL = @SQL + ' SUBSTRING(A,'+CONVERT(varchar(4),@z)+',1) = ' 
			+ ''''+ '1' + '''' + ' AND'
			, @y = @y + 1
	END
SELECT @SQL = LEFT(@SQL,LEN(@SQL)-4)
EXEC(@SQL)

DROP TABLE A
GO

I'll go take a look into the other approach...



Brett

8-)

---

quote:

---

Originally posted by jsmith8858

EDIT -- never mind, misread the question .... i thought you wanted to do an "aggregate AND" of these strings ......

----

you could do something like this:

select min(substring(A,1,1)) + min(substring(A,2,1)) + min(substring(A,3,1)) _+ ....
from yourtable


that just add one "min(substring(A,N,1))" per character position in your char field.

to do a logical OR, use MAX() instead of min.



- Jeff

---

quote:

---

Originally posted by setbasedisthetruepath

No, no- you will need to actually create the integer representation by doing a manual calculation. SQL Server does not have a built-in function to do it.

SO-
if I had "00100001", as an example, I would need to write a UDF that did its thing and returned 33. THEN you can convert 33 to binary, giving you 0x100001.

Never mind the ==, a typo.

Jonathan
Gaming will never be the same

---

CREATE FUNCTION udf_BIN_TO_ASCII(@a char(16))
RETURNS int
AS
BEGIN
	DECLARE @x int, @y bigint
	 SELECT @x = 1, @y = 0, @a = REVERSE(@a)
	  WHILE @x < 16
		BEGIN
			IF SUBSTRING(@a,@x,1) = 1
				SELECT @y = @y + POWER(2,@x-1)
			SELECT @x = @x + 1
		END
	 RETURN @y
END
GO

SELECT dbo.udf_BIN_TO_ASCII(a) FROM A

quote:

---

Originally posted by vladimir_grigoro

Thanks to all of you!!!

I think that we found the solution of the probmel, which is a little bit complicated but works fine for me - I think.

You are great guys. Thank you!!!

The Rebel

---