question

David 2 1 avatar image
David 2 1 asked

How To Return Only 1 Entry Per Row When A Column Can Contain Multiples - PART 2

I have a NAME column in my CUSTOMER table which can contain more than one name, for example 'Michael James' below:

select NAME from CUSTOMER

NAME
Gordon Freeman
Hugh Ness
Frank Kenstein

How is it possible to query this to return only 1 name, the following name, per customer (i.e. How do I return only the results 'Freeman', 'Ness', 'Kenstein' INSTEAD OF the above 'Gordon Freeman', 'Hugh Ness', 'Frank Kenstein')?

TIA

t-sqlsql-server-2000
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
RickD avatar image
RickD answered
declare @taba table (names varchar(100))            
            
insert into @taba            
select 'Gordon Freeman'            
union select 'Hugh Ness'            
union select 'Frank Kenstein'            
            
select reverse(left(reverse(names),charindex(' ',reverse(names),1)-1)) from @taba            
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.