It works with even Edit: odd numbers as well...

You'd just have the most right bit set to 1 as well (2^0=1). Basically starting your "Power table" with 0 instead of...

Let's see what I can do...

Using your example, 46(base10) can be written as 101110(base2). Agreed?

Let's start with...

GSquared (1/13/2010)

---

I'd have to see the tests that were done on performance comparisons between the two. In the tests I've done, and in the documentation I've seen on the...

You're very welcome, Greg!

The FOR XML PATH clause is available since 2K5.

I tried to explain how it works here . Not sure if it helps to understand though...

Regarding "&":...

@Gus (again): Another thought:

If you'd add AND (@Sum & power(2, Number)) > 0 to your WHERE clause in the Rules CTE you'd get rid of the repetitive Col1=@Col1 part...

@Gus:

Just as a side note: AFAIK varchar(max) columns tend to have a bad influence on performance due to the handling of more than one page (8k).

I guess, varchar(8000) will do...

Lowell (1/13/2010)

---

that would occur if any space existed in the last name, before the slash.

a name like Mac Bride/Bob Lee would make that happen... you'll need to tweak it...

I would use a function to get the "binary pattern". Something like the following:

CREATE FUNCTION tvf_binarySplit(@a int)

returns TABLE

as

RETURN

SELECT (@a & power(2,n)) val

FROM

(SELECT 1 AS n UNION ALL...

Here's the solution I'd use as a complete test scenario. (Please note how I defined the sample data in a ready to use format). Also, this is a 2005 solution...

What SQL Server version do you...

tkacyndra (1/13/2010)

---

i'm using enterprise manager import wizard to import, here is an example of what's going on.

my table, for example, has the following fields:

id (self incrementing key field)

name

address

phone

date_modified (has a...

Please have a look at the TallyTable article linked in my signature. It describes a pretty fast method to split a string.

Would you mind sharing how you'll do it?

One reason is to provide a solution for someone having the same isse.

The second reason: there might be better/faster/cleaner solutions out there but...

I have no idea how to program it using a loop. But a set based solution could look like the following (and is usually a lot faster....)

SELECT

STAFF_ID,

stuff(( SELECT ',...

I'll second Johns question regarding more details.

Please provide table definition together with sample data and expected result.

Some of us like to provide tested solutions. So you'll increase the number of...