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

Transact-SQL
1

Hello,

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

image
image876×259 6.62 KB

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

2

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)
    )
AS
BEGIN
    DECLARE @value NVARCHAR(MAX),
        @pos INT = 0,
        @len INT = 0

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

    WHILE CHARINDEX(@delimiter, @string, @pos + 1) > 0
    BEGIN
        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
    END

    RETURN
END
GO
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, ',')),
     cte_fin
     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,
       CASE
         WHEN cnt = 0 THEN 'No'
         WHEN cnt > 1 THEN 'Multiple'
         ELSE partner
       END AS flag
FROM   cte_fin

image

3 
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
OUTER APPLY (
    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
4

thank you it works :slight_smile:

5

thank you this one works also :slight_smile: