Sql Question

Sql Question

• mware

  Thanks very much for your suggestions. I'm raising my issues to the business users.
  

• Amit

  Is there a process we can check the length of the field after the space and if it is more than 3 (for suffix) then put it in last name or use the in condition to check for specific values of suffix.

  same way can we check for Middle initial the length and if it is more than 1 then put in the first name itself.

  
  

• jkotas

  You may even consider writing a simple .NET application to process your data. My experience with cases like this is that the business usually comes back with a series of conditions in which different paths are taken (eg. if "Jnr" is found as the last word, then don't treat it as the surname, or filtering out prefixes of "Mrs.", "Dr." etc.)
  
  

• ehab ghabour

  Yea, the other question is how much data do you have If you have a hundred rows, just use a simple substring and then scan for invalid data. If you have millions, then a decent algorithm like this should be applied, but you might just need to create a table of rows that have been reviewed and force the next user who touches the row to look at the data for correctness.

  Like r sτσρhΞr said, these are really business rule questions, and name data is really tricky since users will try really hard to put the data into the space allotted so I wouldn't be surprised if data like [Joeseph Joey] [San Sebastian III] might be found.

  Good luck :)

  
  
  

• Terry125

  You can do most anything I am not really clear what you want exactly, but you can use charindex, ltrim, substring, and len to build a check:

  declare @name varchar(30)

  select @name = 'Fred Smith'

  select substring(@name,1,charindex(' ',@name))
  select case when len(ltrim(substring(@name,charindex(' ',@name),len(@name)))) <= 3 then
  ltrim(substring(@name,charindex(' ',@name),len(@name)))
  else '' end as ShortName,
  case when len(ltrim(substring(@name,charindex(' ',@name),len(@name)))) > 3 then
  ltrim(substring(@name,charindex(' ',@name),len(@name)))
  else '' end as LongName
  

  And yes, it's ugly as homemade sin, but you can piece together the different length parts and checks to split a string into parts. It is just messy (which you can gather) and why it is always suggested not to do it this way (which you are apparently undoing right now :)

  
  
  

• octagon

  This is really a business rule question, rather than a technical question. You first need to determine the basis on which you will split the data up. The surname case seems easiest, as a surname usually doesn't include a space. You need to come up with an acceptable rule (in conjunction with the business owners of the data) on the basis by which you split up the data. For example:

  • The last word in a surname with a space will be treated as a suffix, except where it is more than 8 characters long, in which case it will be treated as a full surname with no suffix

  The same applies to the first name, but this is more tricky. For example, in the case of "Mary Anne Jane", with no hyphen, you need to decide whether "Anne" is part of the first name, or the middle name. It would be easiest just to come up with a simple rule such as

  • The first space in a First name (Field1) constitutes the separator between a first name, and a middle name (or set of middle names).
  • A hyphen will not be used to delimit names (ie. "Mary-Anne" would be treated as a full first name).

  ... I would suggest proposing something logical based on your review of the data, then proposing it to the data stakeholders for the confirmation, before implementation.

  
  
  
• Sql Question