CODEWITHSUNDEEP
sql-server
Jagan868
Jagan868

Reputation: 135

How to check for a specific condition by looping through every record in SQL Server?

I do have following table 

ID  Name 
1   Jagan Mohan Reddy868 
2   Jagan Mohan Reddy869 
3   Jagan Mohan Reddy

Name column size is VARCHAR(55).
Now for some other task we need to take only 10 varchar length i.e. VARCHAR(10).
My requirement is to check that after taking the only 10 bits length of Name column value for eg if i take Name value of ID 1 i.e. Jagan Mohan Reddy868 by SUBSTRING(Name, 0,11) if it equals with another row value. here in this case the final value of SUBSTRING(Jagan Mohan Reddy868, 0,11) is equal to Name value of ID 3 row whose Name is 'Jagan Mohan Reddy'. I need to make a list of those kind rows. Can somebody help me out on how can i achieve in SQL Server.

My main check is that the truncated values of my Name column should not match with any non truncated values of Name column. If so i need to get those records.

Upvotes: 0

Views: 88

Answers (4)

Zohar Peled
Zohar Peled

Reputation: 82524

Assuming I understand the question, I think you are looking for something like this:

Create and populate sample data (Please save us this step in your future questions)

DECLARE @T as TABLE
(
    Id int identity(1,1),
    Name varchar(15)
)

INSERT INTO @T VALUES
('Hi, I am Zohar.'),
('Hi, I am Peled.'),
('Hi, I am Z'),
('I''m Zohar peled')

Use a cte with a self inner join to get the list of ids that match the first 10 chars:

;WITH cte as
(
    SELECT T2.Id As Id1, T1.Id As Id2
    FROM @T T1
    INNER JOIN @T T2 ON LEFT(T1.Name, 10) = t2.Name AND T1.Id <> T2.Id 
)

Select the records from the original table, inner joined with a union of the Id1 and Id2 from the cte:

SELECT T.Id, Name
FROM @T T
INNER JOIN
(
    SELECT Id1 As Id
    FROM CTE 
    UNION
    SELECT Id2 
    FROM CTE
) U ON T.Id = U.Id

Results:

Id          Name
----------- ---------------
1           Hi, I am Zohar.
3           Hi, I am Z

Upvotes: 1

momar
momar

Reputation: 364

I think this should cover all the cases you are looking for.

-- Create Table
DECLARE @T as TABLE
(
    Id int identity(1,1),
    Name varchar(55)
)

-- Create Data    
INSERT INTO @T VALUES
('Jagan Mohan Reddy868'),
('Jagan Mohan Reddy869'),
('Jagan Mohan Reddy'),
('Mohan Reddy'),
('Mohan Reddy123551'),
('Mohan R')

-- Get Matching Items
select *, SUBSTRING(name, 0, 11) as ShorterName
from @T 
where SUBSTRING(name, 0, 11) in 
(
    -- get all shortnames with a count > 1
     select SUBSTRING(name, 0, 11) as ShortName 
     from @T   
     group by SUBSTRING(name, 0, 11)
     having COUNT(*) > 1
)
order by Name, LEN(Name)

Upvotes: 0

Roshni Gandhi
Roshni Gandhi

Reputation: 348

loop over your column for all the values and put your substring() function inside this loop and I think in Sql index of string starts from 1 instead of 0. If you pass your string to charindex() like this 

 CHARINDEX('Y', 'Your String')

thus you will come to know whether it is starting from 0 or 1 and you can save your substring value as value of other column with length 10

I hope it will help you..

Upvotes: 0

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28413

Try this

SELECT Id,Name
FROM(
     SELECT *,ROW_NUMBER() OVER(PARTITION BY Name, LEFT(Name,11) ORDER BY ID) RN
     FROM Tbale1 T
    ) Tmp
WHERE Tmp.RN = 1

Upvotes: 0

Related Questions