Wsrtjtyk

I have a table like below

and i want to get the results of table if any of the characters in same position are equal

example:

fullname: ANURADHA KONGARI
names: AK

withoutinitials: AI

here in the example first letter of AK and first letter of AI are equal. so it should return the result.

I have tried using substring but it results all the records. Because of the length

example of what i have tried

select fullname, names, withoutinitials from #localtable where substring(names,1,1)= substring(withoutinitials,1,1)

or

substring(names,2,1)= substring(withoutinitials,2,1)

or

substring(names,3,1)= substring(withoutinitials,3,1)

Here is a way I tried. It is working but what if when the string length is greater 4

create table #lt2(fullname varchar(100))

insert into #lt2 select getName=case  

    when len(names)=1 and substring(names,1,1) = substring(withoutinitials,1,1) 

    then fullname 

    when len(names)=2 and ((substring(names,1,1) = substring(withoutinitials,1,1))or(substring(names,2,1) = substring(withoutinitials,2,1))) 

    then fullname 

    when len(names)=3 and ((substring(names,1,1) = substring(withoutinitials,1,1))or(substring(names,2,1) = substring(withoutinitials,2,1)) or (substring(names,2,1) = substring(withoutinitials,2,1))) 

    then fullname 

    when len(names)=4 and ((substring(names,1,1) = substring(withoutinitials,1,1))or(substring(names,2,1) = substring(withoutinitials,2,1)) or (substring(names,2,1) = substring(withoutinitials,2,1))

    or (substring(names,3,1) = substring(withoutinitials,3,1))) 

    then fullname else ''

    end

    from #localtable



select * from #lt2 where fullname!=''

But this results all the records because some records may not have length 3 so, it even returns the names with length 1.

I want to get the results if atleast one character of two strings are equal in same position.

like 1st position of string1 = 1st position of string 2 or 2nd
position of string1 = 2nd position of string 2 in a generalized way.

Thank you.

You need to use 1 and 2 for your positions, and add an additional check for blank spaces when there are not two characters.

declare @table table (fullname varchar(64), names varchar(2), withoutinititals varchar(2))

insert into @table

values

('GANGA RAJAM','GR','AM'),

('ANURADHA KONGARI','AK','AI'),

('PATEL SHIVAJI','R','H'),

('NEW NAME','X','X')



select

    *

    ,substring(names,1,1)

    ,substring(names,2,1)

    ,substring(withoutinititals,1,1)

    ,substring(withoutinititals,2,1)

from @table

where

    (substring(names,1,1) = substring(withoutinititals,1,1))

    or

    (substring(names,2,1) = substring(withoutinititals,2,1) and substring(withoutinititals,2,1) != '')

Notice if you remove this part of the where clause, substring(withoutinititals,2,1) != '', you will get false positives

Right pad with unequal chars '0' for names and '1' for withoutinitials:

select fullname, names, withoutinitials from #localtable where 

substring(names, 1, 1) = substring(withoutinitials, 1, 1)

or

substring(LEFT(CONCAT(names, '0'), 2), 2, 1) = substring(LEFT(CONCAT(withoutinitials, '1'), 2), 2, 1)

or

substring(LEFT(CONCAT(names, '00'), 3), 3, 1) = substring(LEFT(CONCAT(withoutinitials, '11'), 3), 3, 1)

or

substring(LEFT(CONCAT(names, '000'), 4), 4, 1) = substring(LEFT(CONCAT(withoutinitials, '111'), 4), 4, 1)

with sample data:

INSERT INTO localtable (fullname, names, withoutinitials) VALUES ('abc1', 'GR', 'AM');

INSERT INTO localtable (fullname, names, withoutinitials) VALUES ('abc2', 'G', 'A');

INSERT INTO localtable (fullname, names, withoutinitials) VALUES ('abc3', 'GR', 'GM');

INSERT INTO localtable (fullname, names, withoutinitials) VALUES ('abc4', 'R', 'R');

INSERT INTO localtable (fullname, names, withoutinitials) VALUES ('abc5', 'GRAA', 'AMAM');

INSERT INTO localtable (fullname, names, withoutinitials) VALUES ('abc6', 'GRS', 'AMS');

INSERT INTO localtable (fullname, names, withoutinitials) VALUES ('abc7', 'AGR', 'AAM');

INSERT INTO localtable (fullname, names, withoutinitials) VALUES ('abc8', 'GR', 'AM');

the result is:

    fullname    names   withoutinitials

1   abc3        GR      GM

2   abc4        R       R

3   abc5        GRAA    AMAM

4   abc6        GRS     AMS

5   abc7        AGR     AAM

just a bit of fun, but I tried a recursive CTE

--select distinct top 4000 employeeid, surname, ForeName1 forename into #test from isEmployeeMaster where Sequence  = 1 







;WITH S AS (SELECT 1 AS A, M.employeeid as E, M.Surname as sur, SUBSTRING(M.surname,1,1) ATOM FROM #test M 

            UNION ALL

            SELECT A + 1, E, sur, SUBSTRING(S.sur,A+1,1)  FROM S WHERE A < LEN(S.sur) 

            ),

      F AS (SELECT 1 AS A, M2.employeeid as E, M2.forename as fore, SUBSTRING(M2.forename,1,1) ATOM FROM #test M2

            UNION ALL

            SELECT A + 1, E, fore, SUBSTRING(f.fore,A+1,1)  FROM f WHERE A < LEN(f.fore) 

            )



            select t.* from #test t where t.EmployeeId  in 

            (select s.e  from S join F on S.E = F.E and S.Atom = F.Atom and S.A = F.a)







--drop table #test