CODEWITHSUNDEEP
sqlsql-server
Nicolas Soupart
Nicolas Soupart

Reputation: 21

How to compare two multiple values columns in SQLSERVER

I have a column that contains some value like : ;1;3;7;2; And another column with values like : ;5;2;3;

I need to know if at least one of the number in the second column (5,2 or 3) is contained in the first column. Of course this is an example, I have to do it for several records.

Do you have an idea ?

Here is my code :

SELECT * 
FROM COMPANIES 
WHERE F_SKILLS IN F_CONVENTION

Upvotes: 2

Views: 1530

Answers (4)

D.L.
D.L.

Reputation: 324

If it's a finite set of just a few numbers, you might be able to get away with something as simple as:

SELECT * FROM companies
 WHERE (f_skills LIKE '%;1;%' AND f_convention LIKE '%;1;%')
    OR (f_skills LIKE '%;2;%' AND f_convention LIKE '%;2;%')
    OR (f_skills LIKE '%;3;%' AND f_convention LIKE '%;3;%')
    ...

If that doesn't work... Well, looks like some of the other answers on the page may be bit more comprehensive... Almost embarrassingly so. Although, if the numbers are really just 1-9 like the question suggests, I stand by my answer. :) I know it looks a little pitiful in comparison, but, seriously, it just might work! If not, I'd start with the one from Mr. Bhosale.

Upvotes: 0

FJT
FJT

Reputation: 2083

If you're using SQL Server 2016, check out the string_split function.

Assuming you're not, you can create a split function as per previous answer.

If this isn't an option you can do it with a CTE, but it is likely to be inefficient if you have a large dataset.

create table test(col1 varchar(100), col2 varchar(100));
insert into test values ('a;b;c', 'c;d;e'),('a;b;c','d;e;f'), ('a;b;c', 'b;a;d')


;WITH SplitSting AS
(
    SELECT
        col1, col2, LEFT(col1,CHARINDEX(';',col1)-1) AS value
            ,RIGHT(col1,LEN(col1)-CHARINDEX(';',col1)) AS remainder
        FROM test
        WHERE col1 IS NOT NULL AND CHARINDEX(';',col1)>0
    UNION ALL
    SELECT
        col1, col2,LEFT(remainder,CHARINDEX(';',remainder)-1)
            ,RIGHT(remainder,LEN(remainder)-CHARINDEX(';',remainder))
        FROM SplitSting
        WHERE remainder IS NOT NULL AND CHARINDEX(';',remainder)>0
    UNION ALL
    SELECT
        col1, col2,remainder,null
        FROM SplitSting
        WHERE remainder IS NOT NULL AND CHARINDEX(';',remainder)=0
)
SELECT distinct col1, col2 FROM SplitSting
where ';'+col2+';' like '%;'+value+';%'

Upvotes: 0

Mr. Bhosale
Mr. Bhosale

Reputation: 3106

Check This.

Using below query you can find all common numbers appeared in both columns.

First Create Function "[SplitLongString]":

        create  FUNCTION [dbo].[SplitLongString] 
        (
            @DelimitedString VARCHAR(MAX), 
            @Delimiter VARCHAR(100)
        )
        RETURNS 
        @tblArray TABLE 
        (
            ElementID INT IDENTITY(1,1), 
            Element VARCHAR(1000)
        )
        AS
        BEGIN   
            DECLARE
                @siIndex    INT,
                @siStart    INT,
                @siDelSize  INT
            SET @siDelSize  = LEN(@Delimiter)
            --loop through source string and add elements to destination table array
            WHILE LEN(@DelimitedString) > 0
            BEGIN
                SET @siIndex = CHARINDEX(@Delimiter, @DelimitedString)
                IF @siIndex = 0
                BEGIN
                    INSERT INTO @tblArray VALUES(@DelimitedString)
                    BREAK
                END
                ELSE
                BEGIN
                    INSERT INTO @tblArray VALUES(SUBSTRING(@DelimitedString, 1,@siIndex - 1))
                    SET @siStart = @siIndex + @siDelSize
                    SET @DelimitedString = SUBSTRING(@DelimitedString, @siStart , LEN(@DelimitedString) - @siStart + 1)
                END
            END

            RETURN 
        END

after you can cross apply to seprate out commo or semi colon. You will get common element under column element. use these column for your further use.

select A.*,y.Element common_element--,X.Element 
from #COMPANIES  A 
CROSS APPLY SplitLongString(F_SKILLS,';') y
CROSS APPLY SplitLongString(F_CONVENTION,';') X 
where  x.Element=y.Element and ( X.Element!=' ' or X.Element!= null)

Output :

enter image description here

let us know if you have any query.

Upvotes: 3

Zulqarnain Jalil
Zulqarnain Jalil

Reputation: 1691

You can use default function that is dbo.Split('5;2;3;',',')

if you don't have this function you can create your own

Create Function

CREATE FUNCTION SplitString
(    
      @Input NVARCHAR(MAX),
      @Character CHAR(1)
)
RETURNS @Output TABLE (
      Item NVARCHAR(1000)
)
AS
BEGIN
      DECLARE @StartIndex INT, @EndIndex INT

      SET @StartIndex = 1
      IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @Character
      BEGIN
            SET @Input = @Input + @Character
      END

      WHILE CHARINDEX(@Character, @Input) > 0
      BEGIN
            SET @EndIndex = CHARINDEX(@Character, @Input)

            INSERT INTO @Output(Item)
            SELECT SUBSTRING(@Input, @StartIndex, @EndIndex - 1)

            SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input))
      END

      RETURN
END
GO

after creating function you can add Condition to your Query 

select * from yourTableName tbl where (select * from dbo.SplitString(tbl.YourColumnWithSemicoluns,';')) in (select * from dbo.SplitString('5;2;3;',';'))

Upvotes: 1

Related Questions