question

David 2 1 avatar image
David 2 1 asked

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

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

select FORENAME from CUSTOMER

FORENAME

Martyn
Hugh
Michael James
John
Frank

How is it possible to query this to return only 1 name per customer (i.e. How do I return in the results 'Michael' INSTEAD OF 'Michael James')?

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.

David 2 1 avatar image
David 2 1 answered

Got a solution to truncating anything after the space:

select substring(FORENAME, 1, case
when charindex(char(32), FORENAME) > 0 then
charindex(char(32), FORENAME) else len(FORENAME)
end)
from CUSTOMER

10 |1200

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

Madhivanan avatar image
Madhivanan answered

or

select substring(FORENAME, 1, charindex(char(32), FORENAME+' ')-1)            
from CUSTOMER            
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.