select only similar names

Question

Post reply

select only similar names

shashianireddy 30786

Old Hand

Points: 348
More actions
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

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

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

karunanidhi.k Grasshopper Points: 19 More actions · Answer 1

Hi..

try the following query..

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

shashianireddy 30786 Old Hand Points: 348 More actions · Answer 2

shashianireddy 30786

Old Hand

Points: 348

January 17, 2014 at 3:18 am

#1681000

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

o.fimin SSChasing Mays Points: 602 More actions · Answer 3

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+'%'

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

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