SQL to standardize/clean up First/Middle/Last name fields

Question

I'm writing a function in SQL that we can use to validate First/Middle/Last names we have in a given table.

At the moment I'm working on a list of ascii codes for characters that I'm going to consider invalid and strip out of the input.

My plan is to create a table which contains the character codes of those characters I consider to be invalid, and to write a cursor that will replace each invalid character from the current input record its working on.

Should I just work my way through the entire ascii table or has anyone ever seen a similar effort like this that I can look at to build from?

Answer 1

Using code in SQL to scan data for invalid characters is a very slow approach and unlikely to make you happy.

Most people would, I think, do this validating outside the db.

If you must do it inside the db, look to write a trigger that uses the database's inherent language (Oracle PL/SQL, MSSQL tsql) to check the string, just coding the valid character list into the script.

And what happens when someone with a name with accents or other interesting characters shows up?

Answer 2

This is pretty much what we do

declare @currentCharacter char(1)
declare @alphanumericString VARCHAR(250)
declare @inputStringLength int 
declare @positionIndex int

 --init variables
 select @positionIndex = 1
 select @alphanumericString = ''

 --get the string length
 select @inputStringLength = LEN(@inputString)

 --loop through the set
 while @positionIndex <= @inputStringLength
 begin
  --get each character 
  select @currentCharacter = substring(@inputString,@positionIndex,1)

  --make sure its between 0-9, A-Z, or a-z
  if (ascii(@currentCharacter) > 31 and ascii(@currentCharacter) < 126)

   set @alphanumericString = @alphanumericString + @currentCharacter

  --increament counter
  set @positionIndex = @positionIndex + 1
 end

 return @alphanumericString
end

Of course you want to do this on data entry not to the whole table as that will take forever.