The main reason why I try to avoid SQL_VARIANT is the need to cast...

Maybe this argument will help, too:

Within one query, SQL server will only use one index per table. So, if you have the following querySELECT col1, col2, col3, col4

FROM table

WHERE col1='something'...

Glad I could help 🙂

And I'd like to thank you for taking the time to set up sample data in a ready to use format. Made it easy to work...

Is it something like the following that you're looking for?

Please note that I used the STR() function to convert the numeric value to to a string data type:

DECLARE @tbl TABLE

(

...

Please help us help you and provide ready to use sample data.

The most efficient way how to do that is described in the first link in my signature.

Providing some Excel...

@peter-2:

BTW, where did you get [0-Z] from. I was surprised to see that ':' or ';' do not fall within that range when using a collation like Latin1_General_CI_Ax. I scanned...

You should use equal comparison rather than not equal.

CASE isComments WHEN 1 THEN rd.responseComments

WHEN 0 THEN ''

...

Thanx for clarification, Peter!

I knew there must have been a reason Lowell provided a method that seemed to leave room for improvement... :blush:

I would look into a Tally table solution with CROSS APPLY or a split string function together with an intermediate table and a quirky update or any combination of those...

The main question is: what logic do you use to define the week number?

Are you using ISO week, SQL standard (January 1st = week 1), DATEFIRST dependent or not or...

What's on your list so far?

Lynn Pettis (3/23/2010)

lmu92 (3/23/2010)

Why would you use two functions for this task rather than doing it all at once?

My wild guess would be that the pure UNPIVOT solution should be...

@Lynn:

Why would you use two functions for this task rather than doing it all at once?

My wild guess would be that the pure UNPIVOT solution should be more efficient. What...

Maybe you could make the CASE statement of Lowells nice function a little shorter:

CASE

WHEN (SUBSTRING(@OriginalText,Tally.N,1)) like '[0-Z]'

THEN SUBSTRING(@OriginalText,Tally.N,1)

ELSE '' END

It seems...

Would something like this help?

DECLARE @tbl TABLE

(id INT,

c1 DATETIME,

c2 DATETIME,

c3 DATETIME,

c4 DATETIME

)

INSERT INTO @tbl

SELECT 1,GETDATE(), GETDATE()-1,GETDATE()-2,GETDATE()+3 UNION ALL

SELECT 2,GETDATE()-5, GETDATE()-6,GETDATE()-4,GETDATE()-3

;WITH cte AS

(

SELECT id,DateVal

FROM...