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 MTNOH_AAA_Platform.dbo .varcharmax2table(‘北京，上海，广州，深圳‘, ‘,‘))
原文：http://www.cnblogs.com/EminemJK/p/5443681.html