Let’s write a Table-valued function below. This can be created by navigating to Functions – Table-Valued Functions inside SQL Management Studio.
Just an FYI, I am using the AdventureWorks database, but this sample can be used in any situation.
CREATE FUNCTION [dbo].[fn_SplitStateProvinceCode] ( @List VARCHAR(MAX), @Delimiter CHAR(1) ) RETURNS TABLE AS RETURN ( SELECT Item = CONVERT(varchar(500), Item) FROM ( SELECT Item = x.i.value('(./text())', 'varchar(max)') FROM ( SELECT [XML] = CONVERT(XML, '' + REPLACE(@List, @Delimiter, '') + '').query('.') ) AS a CROSS APPLY [XML].nodes('i') AS x(i) ) AS y WHERE Item IS NOT NULL );
For testing, you can declare a variable and test the statement by passing the @List and delimiter like so. As you can see, in the sample below, the delimiter is ‘,’.
Caution: The delimited string is strictly delimited by a comma and has no space in between province codes.
DECLARE @List VARCHAR(MAX) SET @List = 'AB,ON,TX,VIC,WA' SELECT CODES = ITEM FROM dbo.[fn_SplitStateProvinceCode (@List, ',')
If you wish to write it in a query using the Where clause and using ‘IN’ you can do the following:
DECLARE @List VARCHAR(MAX) SET @List = 'AB,ON,TX,VIC,WA' Select * from Person.StateProvince WHERE StateProvinceCode IN (Select CODES = ITEM FROM dbo.fn_SplitStateProvinceCode (@List, ','))