Hakim:

Why do you need to join the system view syscomments twice? I think you only need to alias it once. Here is the modified code:

selectdistinct

ObjectType= o.type_desc

,ObjectName= o.name

,CodeSequence= c_display.colid

,Code=...

Bob:

Thanks for your reply.

Sorry I sent you a salutation of "SSCrazy"!

--Jeff

SSCrazy:

Look at Julie Breuntzmann's post just before mine. I think you also need to cast the varchar(max) to a text data type:

update sample

SET data = CAST(REPLACE(CAST(data AS VARCHAR(MAX)),'b','|') AS...

Try this solution that first copies the table to a temp table (or table variable), but defines the text column as a varchar(max). Then you can use the REPLACE...

Knockyo:

Try this. For @SelectColNames, you simply want to replace all occurrences of '] ,' with ']+'.

Therefore,

select DISTINCT

CapitalForecastID

,Description

,BudgetOwnerName

,CapitalForecastCostPerUnit

,' +

'SUM(' +

LEFT(REPLACE(@SelectColNames, '] ,', ']+'),...

Rather than using complex CASE WHEN logic to generate your ORDER BY clause, why not use the following IF...ELSE logic?

declare @t table (c1 int, c2 varchar(50))

declare @sortorder int

insert...

One final update to my solution. Using CTE's, the output query is much easier to understand.

Here is the complete script:

create table #temp

(

name varchar(4),

age int,

sex varchar(1)

)

insert #temp

select 'ABC',24,'M'

union all

select...

Thanks for your comments. The following modified solution works when you use the row_number() function to number the rows in an output query.

For the following query, the output for...

Maybe I'm missing something, but the query the original poster was looking for is simple with the appropriate GROUP BY and HAVING clauses.

SELECT Name, Age, Sex

FROM Source

GROUP BY Name,...

Lynn:

Oops!!!!

I misread his second note that says he wanted to remove a fixed position, whether or not it's an 'H'. My solution would work if he wanted to remove...

Lynn:

I'm not trying to give you a hard time, but look at his second post that I copied above!!!!

--Jeff

Lynn:

The original poster's second note says:

"The problem is that this character is not always H, it can be any character. How can I use the Len function to get a...

The STUFF function should only be executed if the 10th character is an 'H'. Therefore, the STUFF function should be used in combination with a SUBSTRING function and a...

Jacob:

Great article!

I think I spotted a small error in sprocs ProductSearch6 and ProductSearch7 when a search parameter is not Null, and you are using the LIKE operator. You should...

I agree with SSC Eights for the third solution!!!!!

Here it is:

SELECT DATEDIFF(yy, DateOfBirth, GETDATE()) -

CASE WHEN (DATEPART(m, DateOfBirth) > DATEPART(m, GETDATE()) OR

(DATEPART(m, DateOfBirth) = DATEPART(m, GETDATE())

AND (DATEPART(d,...