Selecting a dynamic row has a different meaning if specified in the Where clause

Question

I am dynamically selecting a row constructed using another row. So, if string1 = 'David Banner', then there MyDynamicStringshould 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 that table2.someotherfield is not equal for a dynamic row. However, when I replace MyDynamicString in the Where clause with the full left (left (etc.) Function, it works as expected.

Can I refer to this line later in the request? Should I build it using the left (left (etc.) Function every time in the where clause?

Answer 1

, , , where.

:

    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

SQL 2008 ORDER BY CLAUSE, where.

(UDF), DRY, ?

, article, , HAVING, WHERE, GROUP BY.