참고만 하세요..

실력이 부족해서 쿼리가 복잡해요 -_-;;;

----쿼리 시작----

with tblA (PK_ID,addr) As
(
       Select '1','경기도 군포시 오류2동' Union All                                          
       Select '2','경기도 군포시 오류5동' Union All                                          
       Select '3','서울 특별시 서초구 방배2동' Union All                                          
       Select '4','경기도 안산시 상록구 본오2동'
)      

,tb_data2 (id,addr,seq,leng,l_data) As
(
     Select a.PK_ID     As id
           ,SubString(a.addr,1,charindex(' ',a.addr)-1) As addr
           ,1        As seq
           ,charindex(' ',a.addr)+1  As leng
           ,SubString(a.addr,charindex(' ',a.addr)+1,LEN(a.addr)) As l_data
       From tblA a
 
  Union All
 
     Select a.id     As id    
           ,(
                Case When charindex(' ',a.l_data) = 0 And IsNull(a.l_data,'') <> ''
                        Then SUBSTRING(a.l_data,1,Len(a.l_data))
                     When charindex(' ',a.l_data) <> 0 And IsNull(a.l_data,'') <> ''
                        Then SubString(a.l_data,1,charindex(' ',a.l_data)-1)
                End
            )  As addr
           ,a.seq + 1   As seq
           ,charindex(' ',a.l_data)+1 As leng
           ,(
                Case When charindex(' ',a.l_data) = 0 And IsNull(a.l_data,'') <> ''
                        Then ''
                     When charindex(' ',a.l_data) <> 0 And IsNull(a.l_data,'') <> ''
                        Then SubString(a.l_data,charindex(' ',a.l_data)+1,LEN(a.l_data))
                End
            )
       From tb_data2 a
      Where ISNULL(a.l_data,'') <> ''
)
   Select a.id
         ,a.addr
     From tb_data2 a
    Where a.addr Like '%동%'
 Order By a.id Asc
 ----쿼리 끝----

고맙습니다  그런데 봐도 모르겠어요 흐흐흐 고맙습니다

위의 예시랑 비슷하게 adventure2008 데이터베이스 이용해봤습니다. 참고하세요

use AdventureWorks2008

go

select Name From Sales.Store

Name

--------------------------------------------------

Next-Door Bike Store

Professional Sales and Service

Riders Company

The Bike Mechanics

Nationwide Supply

Area Bike Accessories

Bicycle Accessories and Kits

Clamps & Brackets Co.

Valley Bicycle Specialists

New Bikes Company

Vinyl and Plastic Goods Corporation

Top of the Line Bikes

Fun Toys and Bikes

select substring(name, len(name)-CHARINDEX(' ',reverse(name))+2, CHARINDEX(' ',reverse(name))) as Name

from Sales.Store

Name

--------------------------------------------------

Store

Service

Company

Mechanics

Supply

Accessories

Kits

Co.

Specialists

Company

Corporation

Bikes