CREATE FUNCTION [dbo].[varcharmax2table]

    (

      @value VARCHAR(MAX) ,

      @separator VARCHAR(255)

    )

RETURNS TABLE

AS

RETURN

    SELECT  LTRIM(RTRIM(b.value)) value

    FROM    ( SELECT    CONVERT(XML, '<v>' + REPLACE(@value, @separator,

                                                     '</v><v>') + '</v>') value

            ) a

            OUTER APPLY ( SELECT    N.v.value('.', 'varchar(max)') value

                          FROM      a.value.nodes('/v') N ( v )

                        ) b

    WHERE   LTRIM(RTRIM(b.value)) <> ''

GO

select * from table1

where city in ( select value from dbo .varcharmax2table('北京，上海，广州，深圳', ','))

北京

上海

广州

深圳