CREATE TABLE [dbo].[postaltable](
[postalcode] [varchar](50) NULL,
[city] [varchar](50) NULL,
[state] [varchar](30) NULL
)
GO
INSERT [dbo].[postaltable] ([postalcode], [city], [state]) VALUES
('11000', 'shimla', 'HP')
,('22000', 'dehradun', 'UK')
,('33000', 'noida', 'UP')
,('44000', 'panjim', 'Goa')
,('55000', 'Agra', 'UP')
,('66000', 'Jaipur', 'Rajasthan')
,('77000', 'Rampur', 'UP')
Go
CREATE function [dbo].[demofun] (@str varchar(100))
returns @colDetails table
(
postalcode varchar(50),
city varchar(50),
state varchar(50)
)
begin
DECLARE @strXML AS XML
SET @strXML = cast((''+replace(@str,',' ,'')
+'') AS XML)
insert into @colDetails
select * from postaltable where city in
(
SELECT A.value('.', 'varchar(max)') FROM @strXML.nodes('test') AS FN(A)
)
return
end
GO
select * from [dbo].[demofun]('Agra,Jaipur')