October 7, 2011 at 9:55 am
I have been unable to arrive at a working solution to this, and my hunch is there is not enough information to get to a working solution but it is worth a try to see if anyone has some ideas on this.
What I have is a situation where a 3rd party software holds names of individuals and companies in the same table and fields, split out as a first name and last name field. The annoyance comes when attempting to create reports, this combination makes odd looking (and unproffessional) concatenations for names. Primarily there are spaces in the middle of words when the name is a company. There is not a field or flag that would tell me if the record is a company or individual.
Below is a table def and sample data, I included the desired output of each concatenation as a third field in the data.
This is more of an annoyance on my part than a need, but it did get me wondering if there actually is a way to do this.
CREATE TABLE [#Names] (
[First_Name] [char] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Last_Name] [char] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Desired_Result] [varchar] (28) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
INSERT INTO [#Names] (First_Name,Last_Name, Desired_Result)
Select 'CHIEF MEDICAL ' as First_Name, 'OFFICER ' as Last_Name, 'CHIEF MEDICAL OFFICER' as Desired_Result
Union All
Select 'HIGH COUNTRY ' as First_Name, 'HOSPITAL ' as Last_Name, 'HIGH COUNTRY HOSPITAL' as Desired_Result
Union All
Select 'AMERICAN PROST' as First_Name, 'HETICS ' as Last_Name, 'AMERICAN PROSTHETICS' as Desired_Result
Union All
Select 'F BILL ' as First_Name, 'MURRAY ' as Last_Name, 'F BILL MURRAY' as Desired_Result
Union All
Select 'PRAIRIE DU CHI' as First_Name, 'EN MEM HOSP ' as Last_Name, 'PRAIRIE DU CHIEN MEM HOSP' as Desired_Result
Union All
Select 'THOMAS ' as First_Name, 'PRICE JR ' as Last_Name, 'THOMAS PRICE JR' as Desired_Result
Union All
Select 'PRAIRE COMM ' as First_Name, 'AMBULANCE ' as Last_Name, 'PRAIRE COMM AMBULANCE' as Desired_Result
Union All
Select 'CHUCKY DOUGLAS' as First_Name, 'QUNIT III ' as Last_Name, 'CHUCKY DOUGLAS QUNIT III' as Desired_Result
October 7, 2011 at 11:38 am
Without an identifier for whether a record is a business name that needs to be concatenated with out a space then there really is no way to do this.
October 7, 2011 at 1:00 pm
Would the RTRIM function not work?
CREATE TABLE [#Names] (
[First_Name] [char] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Last_Name] [char] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Desired_Result] [varchar] (28) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
INSERT INTO [#Names] (First_Name,Last_Name, Desired_Result)
Select 'CHIEF MEDICAL ' as First_Name, 'OFFICER ' as Last_Name, 'CHIEF MEDICAL OFFICER' as Desired_Result
Union All
Select 'HIGH COUNTRY ' as First_Name, 'HOSPITAL ' as Last_Name, 'HIGH COUNTRY HOSPITAL' as Desired_Result
Union All
Select 'AMERICAN PROST' as First_Name, 'HETICS ' as Last_Name, 'AMERICAN PROSTHETICS' as Desired_Result
Union All
Select 'F BILL ' as First_Name, 'MURRAY ' as Last_Name, 'F BILL MURRAY' as Desired_Result
Union All
Select 'PRAIRIE DU CHI' as First_Name, 'EN MEM HOSP ' as Last_Name, 'PRAIRIE DU CHIEN MEM HOSP' as Desired_Result
Union All
Select 'THOMAS ' as First_Name, 'PRICE JR ' as Last_Name, 'THOMAS PRICE JR' as Desired_Result
Union All
Select 'PRAIRE COMM ' as First_Name, 'AMBULANCE ' as Last_Name, 'PRAIRE COMM AMBULANCE' as Desired_Result
Union All
Select 'CHUCKY DOUGLAS' as First_Name, 'QUNIT III ' as Last_Name, 'CHUCKY DOUGLAS QUNIT III' as Desired_Result
SELECT RTRIM(First_name) + ' ' + RTRIM(Last_Name),
Desired_Result
FROM #Names
October 7, 2011 at 1:20 pm
Rtrim does work in some cases, but not all. That is in some cases of the data, there should be a space added to the concatenation of the two fields, and in other cases there should not be.
I generally don't think it can be done, as venoym said, there needs to be a way to id the item as a person or company. However, I have seen solutions to problems on here which I would also have thought could not be done.
It is a thought puzzle, with a useful result, for me. I keep thinking if the eye and mind can see it near instantly, then there is a rule or method that is not a mental "table scan" of every word we have ever encountered to know the thing is wrong.
October 14, 2011 at 4:20 am
You use RTRIM + ' ' only if 2 or more last characters in the 1st column are spaces.
If LEN >=19 then just concatenate as it is, do not change values.
Else - trim 1st value and add space in between.
_____________
Code for TallyGenerator
October 14, 2011 at 8:14 am
Thank you Sergiy, I will have to try that. Every step closer to getting toward the answer for this is very welcome.
October 14, 2011 at 8:32 am
Closer, but still no cigar:
SELECT
RTRIM(First_name) + ' ' + RTRIM(Last_Name),
RTRIM(First_name) + CASE WHEN RIGHT(First_name,1) = ' ' THEN ' ' ELSE '' END + RTRIM(Last_Name),
Desired_Result
FROM #Names
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 14, 2011 at 8:57 am
Yeah, wouldn't it be nice if people followed some sort of actual logical rules on naming things, as well as the data entry people following those rules, rather than breaking the names and words apart where they have a whim?
October 14, 2011 at 9:04 am
Do you have a list of all of the companies that you can store in your own table? If so, this is trivial. If not, it's impossible due to the occasion that Chris has pointed out (where an individual has 14 characters in their "first name" field and so no space to indicate that it is the end of their first name).
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply