Select dynamic string has a different value when referenced in Where clause

Question

I dynamically select a string built using another string. So, if string1='David Banner', then MyDynamicString should be 'DBanne'

Select 
...
, Left(
    left((select top 1 strval from dbo.SPLIT(string1,' ')) //first word
    ,1) //first character
    + (select top 1 strval from dbo.SPLIT(string1,' ') 
        //second word
        where strval not in (select top 1 strval from dbo.SPLIT(string1,' '))) 
,6) //1st character of 1st word, followed by up to 5 characters of second word
[MyDynamicString]
,...
From table1 Join table2 on table1pkey=table2fkey
Where MyDynamicString <> table2.someotherfield

I know table2.someotherfield is not equal to the dynamic string. However, when I replace MyDynamicString in the Where clause with the full left(left(etc.. function, it works as expected.

Can I not reference this string later in the query? Do I have to build it using the left(left(etc.. function each time in the where clause?

Answer 1

If you do it as you have it above, then the answer is yes, you have to recreate it again in the where clause.

As an alternative, you could use an inline view:

    Select 
    ...
    , X.theString
    ,...
    From table1 Join table2 on table1pkey=table2fkey
       , (SELECT 
          string1
         ,Left(
            left((select top 1 strval from dbo.SPLIT(string1,' ')) //first word
                 ,1) //first character
            + (select top 1 strval from dbo.SPLIT(string1,' ') 
            //second word
            where strval not in (select top 1 strval from dbo.SPLIT(string1,' '))) 
           ,6) theString //1st character of 1st word, followed by up to 5 characters of second word
         FROM table1
       ) X
    Where X.theString <> table2.someotherfield
      AND X.string1 = <whatever you need to join it to>

Answer 2

In SQL 2008 you can use the alias in the ORDER BY CLAUSE, but not in the where clause.

Why not wrap the calculation up into a user defined function (UDF) to avoid violating DRY and also to make the query more readable?

If it matters, here is an article that explains why you can't use a column alias in a HAVING, WHERE, or GROUP BY.