Trim Function Problem

• p.shabbir

  SSCrazy

  Points: 2404

  More actions

  November 11, 2013 at 2:39 pm

  #301082

  Hi All,

  I have two table originalHeader and CurrentHeader of which have one column named Colname and Column_Name. One value in the originalHeader table have space at last. For example

  originalHeader Table CurrentHeader Table

  Colname Column_name

  AAA AAA

  BBB BBB

  CCC CCC

  DDD DDD

  EEE EEE

  If i run the below query the output will return only 4 values.How to get all the values if there are spaces after the value.

  Select OH.ColName from originalHeader OH INNER JOIN CurrentHeader CH ON RTRIM(OH.ColName) = CH.column_name

• mister.magoo

  SSC-Forever

  Points: 47174

  More actions

  November 11, 2013 at 4:25 pm

  #1665611

  You don't need to trim spaces after a value for an equality condition, so I suspect you have a non-printable character in there.

  Try something like this to check that:

  select cast( OH.ColName as VARBINARY( 512 ) ) as hex_value

  from originalHeader OH

  where OH.ColName like 'AAA%'

  (replace AAA with the start of whichever value is missing)

  Then examine the result to see what characters come after the "visible" part of the value.

  If you are not sure, just post the varbinary value here for us to take a look.

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]