SQLTeam.com | Weblogs | Forums

Urgently need help with this sql problem



I have to figure out the items that Legal Name implies individual but Legal Entity Structure indicates a incorporation type. In this sample, you can see Alexander, Justin N. is my target. But my problem is how should I use a query to figure out which one is a individual's name? and any one can provide me the script with this question? Thank you very much!

Legal Name ///////////////////////////////////////////// Legal_Entity_Struct

S & H Farm Supply, Ltd./////////////////////////// Company
F.M.Abbott Power Equipment,Co.////////////// Company
Ray's Dixie Chopper, Inc.////////////////////////// Company
Alexander, Justin N. ///////////////////////////////// Company
Alameda Power Equipment, Inc.//////////////// Company
Dun-Rite Appliance Co. Inc./////////////////////// Company
Edwards Furn. Of Crystal //////////////////////// Company
Baker's, Company ///////////////////////////////////// Company
Tennies Hardware, Inc.////////////////////////////// Company
Heinold and Feller Tire Co., Inc.////////////////// Company
Milton Village Hardware, Inc. ////////////////////// Company
Crane & Son, Inc./////////////////////////////////////// Company
Triple D Equipment, Inc///////////////////////////// Company
Merritt, Stephen C. //////////////////////////////////////Sole Propriertorship
Cane Equipment Cooperative, Inc. //////////////Company


If the format of the data will always be last name, first mi, write a function that checks the field for that format


Yes, the format of the data is always like Last Name, First Middle. Can you show me how to check that? Thank you


Alexander, Justin N. also having Legal Entity Struct as company. But how to identify the value is company name or individual name?

If you are searching for Alexander just using like 'Alexander%' will give you the required result. Kindly elaborate the result which is required

 SELECT Legal Name,Legal_Entity_Struct WHERE Legal_Entity_Struct = 'Company' AND Legal Name NOT LIKE '%Ltd%' OR Legal Name NOT LIKE '%Co.%' OR  Legal Name NOT LIKE '%Inc.%'


Also u can use CONTAINS instead of LIKE for faster result.. CONTAINS(Legal Name,'"Ltd" OR "Co." OR "Inc."');


If there are apt of records padh's approach will not be diligent enough for very high accuracy/. A function that checks there is no more than 2 spaces, does not end with ltd, llc, etc
There is a comma at the end of first word, and if there r 3 words the lad word is no more than 2 length. Tweak it,but you get the point

Also you can add a regex module library and do with a simple regex pattern, or you can even tweak a like to use special search characters and filters and get pretty close