Complicated select and update

I have a totally messed up table I've received that has a column called "Vendor"
The Vendor can be a company name, a PO Box or an actual name

The actual names ALL come in as
First Last
or
First M Last
First M. Last

What I want to do is use the columns where there are 1 or 2 single spaces
left join them to my table variable below with the list of first names as an added "this is a name" filter

and parse out the first, middle, last
DECLARE @firstNames TABLE 
(
  firstName varchar(100)
)

Insert into @firstNames (firstName) 
Select tmp.firstName  from (

Select firstName from portal.dbo.r_users
union all
Select firstName from proc_ss.dbo.P_Deals
union all
select firstName from proc_lt.dbo.P_Deals
union all
select ordFirstName firstName from proc_cfa.dbo.P_Order
union all
select first_name from crmprod_01.dbo.leads
union all
select first_name from crmlotto_prod.dbo.leads) tmp
group by tmp.firstName
order by tmp.firstName

Select * from @firstNames

Open in new window

Larry Bristersr. DeveloperAsked:
Who is Participating?
 
Scott PletcherSenior DBACommented:

DECLARE @firstNames TABLE 
(
  firstName varchar(100) primary key
)

Insert into @firstNames (firstName) 
Select tmp.firstName  from (
Select firstName from portal.dbo.r_users
union
Select firstName from proc_ss.dbo.P_Deals
union
select firstName from proc_lt.dbo.P_Deals
union
select ordFirstName firstName from proc_cfa.dbo.P_Order
union
select first_name from crmprod_01.dbo.leads
union
select first_name from crmlotto_prod.dbo.leads) tmp
order by tmp.firstName

Select * from @firstNames


SELECT tn.*, CASE WHEN fn.firstName IS NULL THEN 'Not Name' ELSE 'Name' END
FROM tablename tn
LEFT OUTER JOIN @firstNames fn ON
    fn.firstName = LEFT(tn.Vendor, CHARINDEX(SPACE(1), tn.Vendor) - 1)
WHERE
    tn.Vendor LIKE '% %' OR
    tn.Vendor LIKE '% % %'

Open in new window

0
 
LowfatspreadCommented:
sorry dont follow what you're after...

could you try specifying what you want again , with some example Input and Output...

(ps... there is no point in using UNION ALL if you are going to distinct with a group by .... just use UNION in the first place
   and don't have the group by... the order by is fairly meaningless as well.)
0
 
Scott PletcherSenior DBACommented:
Change the LEFT OUTER to an INNER join if you want to select only matching first names.
0
 
Larry Bristersr. DeveloperAuthor Commented:
perfect
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.