I am trying to run this query:

select address1,
charindex(',',address1,0) as st,
charindex(',',address1,charindex(',',address1,0)+1) as fn,
substring(address1,st,fn-st) as city
from tableHouses
where address1 like '%,%'

But I get this error:

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'fn'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'st'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'st'.


Is there a way to define variables in a query so I can run the above?

I want to be able to do this so I can make sure the second comma is found
using a where clause at the end of the statement.

Rich

Richard
That is not valid. try following

(untested, pls check if there is any missing comm or brackets.

select address1
charindex(',',address1,0) as st
charindex(',',address1,charindex(',',address1,0)+1) as fn
substring(address1,charindex(',',address1,0)
(charindex(',',address1,charindex(',',address1,0)+1)

(charindex(',',address1,0) )) as cit
from tableHouse
where address1 like '%,%

-- O

select address1,st,fn,substring(address1,st,fn-st) as cit
from
(select address1
charindex(',',address1,0) as st
charindex(',',address1,charindex(',',address1,0)+1) as f
from tableHouse
where address1 like '%,%') X