Is COLLATE able to sort for line break characters?

• zbennett

  SSC Enthusiast

  Points: 172

  More actions

  May 18, 2016 at 1:17 pm

  #327156

  Hi,

  I was working with some address data and came across a few Zip Code entries that were proceeded with a carriage return and line feed. I attempted to remedy this using collation, but I was unable to find a form that would catch line breaks. I did find a fix that would remove the line break characters (replace(replace(ZipCode,char(10),''),char(13),'')), but I'm curious as to whether or not COLLATE would be able to do the same.

  Thanks,

  Zach

• drew.allen

  SSC Guru

  Points: 76944

  More actions

  May 18, 2016 at 1:32 pm

  #1880014

  There are four options associated with (non-binary) collations

• case sensitivity
• accent sensitivity
• kana sensitivity
• width sensitivity

To the best of my knowledge, carriage return and line feed do not have alternate representations that differ on any of these options, so they will be unaffected by changes in the collation.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA