SQL LOGIC hELP

[vuyiswamb]

Good Day All

i have a table that carries a Field that has data like this

10101010101010

Now the Function that adds this 1's and 0's is working like this. If its selected insert "1" else "0", So if i can interpret the above if will be

1 3 5 7 9 11 13

the only thing that am interested in is "1's", if i can display their Position in a SQL Query i will be happy

i want to take these Values from a SQl table and Bind them to a Gridview. Remember the Number of Zeor's can increase , but the Limit is 63 including the "1's" and "0's".

Thank you

[Ramy Mahrous]

use SQL CLR to add your logic :)

[sknake]

If the numbers of 0's can increase that means the length of the string can increase, which lends itself to mean the number of ones can increase. Are you wanting to represent a string based on the position of the characters set to 1?

f.eg: ###

Will represents the numbers 1,2,3

So if you store: 001
It displays: 3?

[vuyiswamb]

hi sknake

I came up with this , and it seems to work

declare @S as nvarchar(63)
set @S = '10101010101010'

declare @NS as nvarchar(128)
set @NS=''
declare @Len as int
SET @Len = LEN(@S)

declare @Counter as int
SET @Counter = 1
WHILE @Counter < = @Len
BEGIN
	if (SUBSTRING(@S,@Counter,1)='1')
		set @NS = @NS + ' ' + convert(nvarchar,@Counter)
	SET @Counter = @Counter + 1
END
select @NS

what do you think about it

[sknake]

That will work as a function so if it meets your needs then great, but I don't really know what you're trying to accomplish in the big picture.

[Ramy Mahrous]

If you create this function through SQL CLR it'd be great, readable code, changes is straightforward, and so on, I don't know really why you didn't reply my post!

[vuyiswamb]

tHANKS:)

[sknake]

If you create this function through SQL CLR it'd be great, readable code, changes is straightforward, and so on, I don't know really why you didn't reply my post!

You need to weigh readability with functionality. The rule of thumb for SQL/CLR is "When you can do it in TSQL then do it in TSQL, if not use CLR". There is a performance difference.