Sql Query To Find Relevant Match

Question

I am trying to write a query that should return the following output:

val1 = 'GREAT' val2 = 'TAGER'

See above have the same characters and it should return 'Exist' if the match is found. Otherwise, if there is a character missing, then 'Not exist'. I am using CASE to get the required output and was wondering if it could be done using SUBSTRING, CHARINDEX etc. Hoping to get a solution or idea to do so. Thanks.

Tag your dbms product! (The answer will probably be product specific!) — jarlh, Mar 01 '16 at 10:47
Are 'AAB' and 'BAB' considered to be equal? Same letters, but different number of them. — jarlh, Mar 01 '16 at 10:49
Why you want to implement this in the DB side I fear to guess but if you want to find anagrams you can sort chars in a (ASCII) order and compare both results — jean, Mar 01 '16 at 10:50
No. Both should have the equal number of characters like 'AAB' or 'ABA' or 'BAA'. — DOTNET Rocker, Mar 01 '16 at 10:52
check this may help http://stackoverflow.com/questions/18575639/t-sql-compare-strings-char-by-char — Taj Ahmed, Mar 01 '16 at 11:18

score 0 · Accepted Answer · answered Mar 01 '16 at 11:02

0

You can make it as a function / procedure according to your needs.

DECLARE @S1 VARCHAR(100)
DECLARE @S2 VARCHAR(100)

SELECT @S1 = val1
    ,@S2 = val2
FROM <TABLE_NAME>

DECLARE @c CHAR(1)
DECLARE @i TINYINT
DECLARE @o1 VARCHAR(100) = ''
DECLARE @o2 VARCHAR(100) = ''

WHILE DataLength(@s1) > 0
BEGIN
    SET @c = Left(@s1, 1)
    SET @s1 = Substring(@s1, 2, len(@s1))
    SET @i = 1

    WHILE @i <= Len(@o1)
        AND @c > substring(@o1, @i, 1)
        SET @i += 1
    SET @o1 = left(@o1, @i - 1) + @c + substring(@o1, @i, len(@o1))
END

WHILE DataLength(@s2) > 0
BEGIN
    SET @c = Left(@s2, 1)
    SET @s2 = Substring(@s2, 2, len(@s2))
    SET @i = 1

    WHILE @i <= Len(@o2)
        AND @c > substring(@o2, @i, 1)
        SET @i += 1
    SET @o2 = left(@o2, @i - 1) + @c + substring(@o2, @i, len(@o2))
END

SELECT CASE 
        WHEN @o1 = @o2
            THEN 'Exist'
        ELSE 'Not Exist'
        END

answered Mar 01 '16 at 11:02

Chendur

1,099
1
11
23

Thanks a lot evil333. It worked. Could you please help me out what the followings are meant actually. Just curious to know: WHILE DataLength(s1) > 0 BEGIN SET c = Left(s1, 1) SET s1 = Substring(s1, 2, len(s1)) SET i = 1 WHILE i <= Len(o1) AND c > substring(o1, i, 1) SET i += 1 SET o1 = left(o1, i - 1) + c + substring(o1, i, len(o1)) END – DOTNET Rocker Mar 01 '16 at 14:21
Checks for every single character. Try printing the variables at each phase [e.g. PRINT @VAR_NAME] so you would understand the working. Good luck – Chendur Mar 02 '16 at 04:54
Thanks again evil333. I got it and am trying to understand. – DOTNET Rocker Mar 02 '16 at 14:18

score 0 · Answer 2 · answered Mar 01 '16 at 11:45

This is a custom script for you Run this SP first

    IF(OBJECT_ID('CharSplit')) IS NOT NULL
    DROP PROCEDURE CharSplit;
GO
CREATE PROC CharSplit
       @Words VARCHAR(MAX)
AS
     BEGIN
         IF OBJECT_ID('tempdb..#temp1') IS NOT NULL
             DROP TABLE #temp1;
         CREATE TABLE #temp1
                            (
                      letter CHAR(1), freq INT
                            );
         IF OBJECT_ID('tempdb..#temp2') IS NOT NULL
             DROP TABLE #temp2;
         CREATE TABLE #temp2
                            (
                      letter CHAR(1), freq INT
                            );
         DECLARE @t VARCHAR(MAX);
         DECLARE @I INT;
         --SET @Words = 'sanuantony';
         SELECT @I = 0;
         WHILE(@I < LEN(@Words) + 1)
             BEGIN
                 SELECT @t = SUBSTRING(@words, @I, 1);
                 INSERT INTO #temp1
                                   (letter, freq
                                   )
                 VALUES
                        (@t, 0
                        );
                 SET @I = @I + 1;
             END;         
         TRUNCATE TABLE #temp2;
         INSERT INTO #temp2
                           (letter, freq
                           )
                SELECT letter, COUNT(freq)
                FROM #temp1
                GROUP BY letter;
         SELECT *
         FROM #temp2
         ORDER BY letter;
     END;

Now Just try your business logic

 DECLARE @t1 AS TABLE
                     (
                      letter CHAR(1), freq INT
                     );
 DECLARE @t2 AS TABLE
                     (
                      letter CHAR(1), freq INT
                     );
 INSERT INTO @t1
 EXEC charSplit 'alammalay';

 INSERT INTO @t2
 EXEC charSplit 'malayalam';

 IF(
    (
     SELECT COUNT(1)
     FROM @t1
    ) =
        (
         SELECT COUNT(1)
         FROM @t2
        )
    AND
        (
         SELECT COUNT(1)
         FROM @t2
        ) =
            (
            (
             SELECT COUNT(1)
             FROM @t1
            )
            UNION
            (
             SELECT COUNT(1)
             FROM @t2
            )
            )
   )
     SELECT 'Both are matching' AS result;
 ELSE
 SELECT 'Both are not matching' AS result;

Sql Query To Find Relevant Match

2 Answers2