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

3
Contributors
7
Replies
8
Views
9 Years
Discussion Span
Last Post by 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?

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

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.

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!

tHANKS:)

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.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.