Infor CRM / SalesLogix – Splitting Up the Values from a Multi-Select Picklist

Customer Question: I have values in a multi-select picklist. Unfortunately many of them no longer exist in my picklist definition. How do I clean these up?

Answer: The challenge here is that these values are saved as comma separate values in a single field in the database.
For example the Account Industry value in the database field could be: Aerospace, Automotive, Information Technology

To separate them and compare them to the master Account Industry picklist definition, it took some creative SQL.

Step 1: Define a SQL Function that Splits the values

CREATE FUNCTION [dbo].[Split]
(
    @String NVARCHAR(4000),
    @Delimiter NCHAR(1)
)
RETURNS TABLE
AS
RETURN
(
    WITH Split(stpos,endpos)
    AS(
        SELECT 0 AS stpos, CHARINDEX(@Delimiter,@String) AS endpos
        UNION ALL
        SELECT endpos+1, CHARINDEX(@Delimiter,@String,endpos+1)
            FROM Split
            WHERE endpos > 0
    )
    SELECT 'Id' = ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
        'Data' = SUBSTRING(@String,stpos,COALESCE(NULLIF(endpos,0),LEN(@String)+1)-stpos)
    FROM Split
)
GO

Step 2: Use this Function to create a memory table called #tempPicklistItems and populate it using a cursor.

All credit for this code goes to Adam Pinilla – SQL Master!!

CREATE TABLE #tempPicklistItems (

	ACCOUNTID CHAR(12),
	PICKLISTTEXT VARCHAR(150)
)

DECLARE @accountid CHAR(12)
DECLARE @accounttext VARCHAR(150)

DECLARE picklist CURSOR
	FOR Select ACCOUNTID, INDUSTRY FROM sysdba.ACCOUNT where ISNULL(INDUSTRY,'')''
	OPEN picklist


FETCH NEXT FROM picklist INTO @accountid, @accounttext

WHILE @@FETCH_STATUS = 0
BEGIN
	
	insert into #tempPicklistItems
	select @accountid, ltrim(rtrim(DATA))
	from dbo.Split(@accounttext,',')
	


	FETCH NEXT FROM picklist INTO @accountid, @accounttext
END

Close picklist; 
DEALLOCATE picklist;

Step 3: Now that we have our memory table, we can join to it and display our results!

select a.ACCOUNTID,ACCOUNT,p.PICKLISTTEXT from sysdba.ACCOUNT a inner join #tempPicklistItems p on p.ACCOUNTID=a.accountid

Caution: Remember that this is a memory table and it will only work in the same session! It will not be available when you open a new SQL Management Studio window.

Hope this is useful the next time someone needs to clean up data in multi-select picklists!