3

Difficult to explain but lets say I have a table with a column called NAME which contains both name and surname and also company names.

In order to eliminate company names I want to remove all rows if that field does not made out of 2 part string Name Surname (i.e. has more than 2 words in it)

Example of table colums:

ID - Name - Someothercolumns

The name may contain both first name and surname and/or sometimes company names like JT trading inc

Since person name contains 2 section (aaaa bbbbb) I was thinking using this logic to filter out from the deletion only person names instead of companies.

Gallus
  • 335
  • 3
  • 21
Joni Jeni
  • 49
  • 2
  • 1
    Can you add sample table data, and explain which of the rows to remove? – jarlh Jan 29 '16 at 12:32
  • @CecilioPardo has the correct answer. I'm not sure why it was deleted. – Gordon Linoff Jan 29 '16 at 12:38
  • 1
    You would define a string by the space between them, so you can search for occurrences of that character like in this answer (http://stackoverflow.com/questions/12344795/count-the-number-of-occurences-of-a-string-in-a-varchar-field). Just don't forget to Trim() your field beforehand to remove preceding and ending spaces. Then you can just add a HAVING statement where Ct > 1. –  Jan 29 '16 at 12:43
  • Hi, example is like this: ID - Name - etc..columns now this name contains both people name and surnme and sometimes company names like DDT retail center etc.. – Joni Jeni Jan 29 '16 at 13:06
  • 1
    `if a column field has more than 2 strings?` You are confusing strings (which can contain spaces and other delimiters) with words. – symcbean Jan 29 '16 at 13:18

1 Answers1

1

You can select rows where the Name has more than two words in it. This will pick up anything that doesn't appear to be a person.

It doesn't account for cases where the company name is the only thing entered and that is 1 or 2 words in length. It also might not take into account situations where the middle name of the person is also stored (I.e. 3 words in length) and possible entry of double-barrel surnames without hyphenation (such as Isaac Forsyth Mimsy)

Using some generic terms as the data structure isn't fully explained in the question:

DELETE #DONT# FROM Table
WHERE (    
ROUND (   
    (
        CHAR_LENGTH(LTRIM(RTRIM(Name)))
        - CHAR_LENGTH( REPLACE ( Name, " ", "") ) 
    ) / CHAR_LENGTH(" ")        
) > 1

I haven't included the COMMIT to stop people copy pasting without thinking it through.

Gallus
  • 335
  • 3
  • 21