Post reply

select only similar names

  • January 17, 2014 at 1:07 am

    #288433

    select similar names in sql table

    i have table like this below

    name

    shashi kiran

    raju

    pranay

    gv raju

    vamshi

    vanshi kri

    shashi

    i want output like this

    name

    shashi

    shashi kiran

    raju

    gv raju

    vamshi

    vamshi kri

  • January 17, 2014 at 2:13 am

    #1680987

    Hi..

    try the following query..

    Select * from tablename where name like '%s' or name like '%r'

  • January 17, 2014 at 3:18 am

    #1681000

    in my table have more then 2000 name .........

  • January 19, 2014 at 10:49 pm

    #1681431

    Hi.

    Try this:

    with names as (select 'shashi kiran' name

    union

    select 'raju'

    union

    select 'pranay'

    union

    select 'gv raju'

    union

    select 'vamshi'

    union

    select 'vamshi kri'

    union

    select 'shash')

    select names1.name from names as names1, names as names2

    where names1.name<>names2.name and names1.name like '%'+names2.name+'%'

    union

    select names2.name from names as names1, names as names2

    where names1.name<>names2.name and names1.name like '%'+names2.name+'%'

  • January 20, 2014 at 2:22 am

    #1681455

    SELECT d.name

    FROM (

    SELECT

    n2.name,

    ct = COUNT(*) OVER(PARTITION BY n1.name)

    FROM names n1

    INNER JOIN names n2 ON n1.name LIKE '%'+n2.name+'%'

    ) d

    WHERE ct > 1

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply