CODEWITHSUNDEEP
sql-server
user1928185
user1928185

Reputation: 112

Make unique colume in SQL

I have table which has a duplicate data.

This is my Now table

Id  Name
1   shahin Zen
2   shahin Zen & Aaron Henley
3   Fred Sayz feat. Antonia Lucas
4   Fred Sayz feat. Lawrence Alexander
5   Fred Sayz feat. Sibel

Note: I can not use distinct beacuse name has not fully match.

I want to make a table form this table like,

ID  Name
1   shahin
2   Fred

Please anyone solved this kind of problem.

Thanks advance

Upvotes: 0

Views: 60

Answers (3)

Eric J. Price
Eric J. Price

Reputation: 2785

You need to account for those records that don't have a space...

Select  Distinct Left(name,CharIndex(' ',name+' '))
From    myTable

Upvotes: 0

SchmitzIT
SchmitzIT

Reputation: 9602

If you just need the first names, try this:

SELECT 
   LEFT(name, CHARINDEX(' ', name)) 
FROM Table1 
GROUP BY LEFT(name, CHARINDEX(' ', name))

Upvotes: 0

Noxthron
Noxthron

Reputation: 492

if you just want to get distinct first words of the rows:

select distinct substring(Name, 0, charindex(' ', Name, 0))
from myTable

you can also add a check for the rows that contains space character by adding a where clause:

where charindex(' ', myTable, 0) > 0

Upvotes: 1

Related Questions