SQLTeam.com | Weblogs | Forums

How to see if some words from a list is contained in a field and update based on the occurances


I have a table with some partners and I need to update a column depending on a list of only some names of partners.

I need to update the Flag column based on the list below (My list).
If in the Partner column I don't see any of the partners from my list then I have to put No.
My problem is to find out when more than one partner from that list is contained in the field.
If the field contains only one partner from my list, then The Flag column should be updated with that name. Example line 4 or 5: In line 4 I have Citrix Systems, Citrix is in My list then I put in the flag column "Citrix". The same is in line 5.
Line 6 and 7 don't contain any of the partners from my list so I put No.
But in line 8, I have "Citrix" and also "Red Hat" that are also in my list, so I need to update with "Multiple".

Does anyone have any idea on how to do this?

Thank you

hi hope this helps

please click arrow for Create Function Script
CREATE FUNCTION [dbo].[fn_split_string_to_column] (
    @string NVARCHAR(MAX),
    @delimiter CHAR(1)
RETURNS @out_put TABLE (
    [column_id] INT IDENTITY(1, 1) NOT NULL,
    [value] NVARCHAR(MAX)
        @pos INT = 0,
        @len INT = 0

    SET @string = CASE 
            WHEN RIGHT(@string, 1) != @delimiter
                THEN @string + @delimiter
            ELSE @string

    WHILE CHARINDEX(@delimiter, @string, @pos + 1) > 0
        SET @len = CHARINDEX(@delimiter, @string, @pos + 1) - @pos
        SET @value = SUBSTRING(@string, @pos, @len)

        INSERT INTO @out_put ([value])
        SELECT LTRIM(RTRIM(@value)) AS [column]

        SET @pos = CHARINDEX(@delimiter, @string, @pos + @len) + 1

please click arrow for Drop Create Sample Data
create table Sample_Data 
Partner varchar(100)

insert into Sample_Data select 'Citrix Systems' 
insert into Sample_Data select 'CloudGenera || Cloudera'
insert into Sample_Data select 'CSC'
insert into Sample_Data select 'Consulting Group || SAP AMERICA INC'
insert into Sample_Data select 'Citrix Systems India || CSC ||Red Hat' 

select *from Sample_Data
DECLARE @string VARCHAR(max) = 'Citrix,Cloudera,Red Hat';

WITH cte
     AS (SELECT *
         FROM   [Fn_split_string_to_column](@string, ',')),
     AS (SELECT b.partner,
                Count(a.value) AS cnt
         FROM   cte a
                RIGHT JOIN sample_data b
                        ON b.partner LIKE '%' + a.value + '%'
         GROUP  BY b.partner)
SELECT partner,
         WHEN cnt = 0 THEN 'No'
         WHEN cnt > 1 THEN 'Multiple'
         ELSE partner
       END AS flag
FROM   cte_fin 


create table #Sample_Data (
    Partner varchar(100)
insert into #Sample_Data values
    ('Citrix Systems'), ('CloudGenera || Cloudera'), ('CSC'), 
    ('Consulting Group || SAP AMERICA INC'), ('Citrix Systems India || CSC ||Red Hat')

if object_id('tempdb.dbo.#my_list') is not null
    drop table #my_list;
create table #my_list ( partner_prefix varchar(100) not null primary key );
insert into #my_list values('Citrix'),('Cloudera'),('Red Hat')

SELECT sd.Partner, CASE WHEN ml.partner_prefix_min <> ml.partner_prefix_max 
    THEN 'Multiple' ELSE ml.partner_prefix_min END AS Flag
FROM #sample_data sd
    SELECT MIN(ml.partner_prefix) AS partner_prefix_min, 
        MAX(ml.partner_prefix) AS partner_prefix_max
    FROM #my_list ml
    WHERE '||' + REPLACE(sd.partner, '|| ', '||') LIKE '%||' + ml.partner_prefix + '%'
) AS ml

thank you it works :slight_smile:

thank you this one works also :slight_smile: