searching for ASCII ranges in a string

• Edward-445599

  Hall of Fame

  Points: 3147

  More actions

  January 27, 2009 at 5:10 am

  #73444

  Hi,

  I need help with a query

  I have a table with a surname field I need to check if any of the characters in the surname are not capital letters. If they are then I want to delete that character and then update the surname with the new string

  Currently I have a cursor which loop for every surname and compares each charter

  WHILE @Counter <= len(@Search_Surname)

  BEGIN

  If ASCII(SUBSTRING(@Search_Surname, @Counter, 1)) 90

  BEGIN

  set @Search_Surname = Left(@Search_Surname, @Counter - 1) + '' + SUBSTRING(@Search_Surname, @Counter + 1, len(@Search_Surname))

  End

  set @counter = @counter + 1

  END

  I then update the table with new surname.

  THE PROBLEM IS that there are millions of rows and this update take more than a day, which is a shame as most the records are already in the right format is there a better way to do this?

  Many thanks

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  January 27, 2009 at 5:44 am

  #933112

  You could use a tally table:

  DECLARE @Search_Surname VARCHAR(40)

  SET @Search_Surname = 'MIxED CaSE SUrNAME'

  SELECT @Search_Surname = STUFF(@Search_Surname, NUMBER, 1, '#')

  FROM NUMBERS

  WHERE NUMBER <= LEN(@Search_Surname)

  AND ASCII(SUBSTRING(@Search_Surname, NUMBER, 1)) > 90

  SELECT REPLACE(@Search_Surname, '#', '')

  'MIED CSE SUNAME'

  REPLACE works well, but watch your default collation on this one:

  DECLARE @Search_Surname VARCHAR(40)

  SET @Search_Surname = 'MIxED CaSE SUrNAME'

  SELECT @Search_Surname = REPLACE(REPLACE(REPLACE(REPLACE(@Search_Surname COLLATE SQL_Latin1_General_CP1_CS_AS, 'a', ''), 's', ''), 'x', ''), 'r', '')

  SELECT @Search_Surname

  I'd recommend you give REPLACE a try on a small test subset of your data - although the statement will appear long and unwieldy, the performance may surprise you.

  UPDATE MyTable SET surname = REPLACE(REPLACE(REPLACE(surname, 'a', ''), 'b', ''), 'c', '') etc

  Cheers

  ChrisM

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  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