select multiple datafields into 1 string with # delimiter RSS

• • Reply
  

  pulsmartin

  Member

  108 Points

  309 Posts

  select multiple datafields into 1 string with # delimiter

  Dec 31, 2012 10:34 AM|LINK

  I want to select different fields into one string, between the fields I want to add the character #.

  Here is what I tried (shortened for clarity). 

  "SELECT (object_category + char(35) + object_type + char(35) + offer_type + char(35)  as MyString from mytable INNER JOIN secondtable ON mytable.ID=secondtable.tbl1ID WHERE mytable.ID=@ID AND secondtable.IstAktiv='1' "

   I get the following error : (translated from another language, so maybe it's not 100%)

  Error when converting the varchar-value '#' in datatype int

• • Reply
  

  usman400

  Contributor

  3503 Points

  721 Posts

  Re: select multiple datafields into 1 string with # delimiter

  Dec 31, 2012 10:41 AM|LINK

  First you have missed the enclosing brackets ( I have underlines its start):

  SELECT (object_category + char(35) + object_type + char(35) + offer_type + char(35)  as MyString
  
  You can use
  SELECT object_category + '#' + object_type + '#' + offer_type  as MyString  ....

  Usman Waheed
  • Edited by usman400 on Dec 31, 2012 10:42 AM
  • Edited by usman400 on Dec 31, 2012 10:42 AM
• • Reply
  

  yrb.yogi

  Star

  14460 Points

  2402 Posts

  Re: select multiple datafields into 1 string with # delimiter

  Dec 31, 2012 10:49 AM|LINK

  --datatype is not maintioned so using cast in all column
  --use cast function numeric datatype
  
  
  
  SELECT ISNULL(CAST(object_category AS VARCHAR(MAX)),'') + '#' +
  ISNULL(CAST(object_type AS VARCHAR(MAX)),'')+'#' + ISNULL(CAST(offer_type AS VARCHAR(MAX)),'') AS MyString
  from mytable
  INNER JOIN secondtable ON mytable.ID=secondtable.tbl1ID
  WHERE mytable.ID=@ID
  AND secondtable.IstAktiv='1'

  Yogesh Bhadauriya
  .Net All About
  
  • Marked as answer by pulsmartin on Dec 31, 2012 05:11 PM
• • Reply
  

  oned_gk

  All-Star

  31651 Points

  6468 Posts

  Re: select multiple datafields into 1 string with # delimiter

  Dec 31, 2012 10:57 AM|LINK

  One or more of your field is int. If you have any non character datatype convert it to varchar first.

  CONVERT(varchar(30), Yourintfield) + .... + ......

  http://msdn.microsoft.com/en-us/library/ms187928.aspx

  Programming to simplify, dont look for hard way
  • Edited by oned_gk on Dec 31, 2012 10:58 AM
  • Edited by oned_gk on Dec 31, 2012 10:59 AM
  • Edited by oned_gk on Dec 31, 2012 11:03 AM
  • Edited by oned_gk on Dec 31, 2012 11:05 AM
  • Edited by pulsmartin on Dec 31, 2012 05:11 PM
  • Edited by pulsmartin on Dec 31, 2012 05:11 PM
• • Reply
  

  pulsmartin

  Member

  108 Points

  309 Posts

  Re: select multiple datafields into 1 string with # delimiter

  Dec 31, 2012 04:28 PM|LINK

  Is "ISNULL" necessary for any field that could be null?

• • Reply
  

  TabAlleman

  All-Star

  15571 Points

  2700 Posts

  Re: select multiple datafields into 1 string with # delimiter

  Dec 31, 2012 04:54 PM|LINK

  pulsmartin

  Is "ISNULL" necessary for any field that could be null?

  Yes, otherwise a null will cause the entire value to be null.

  -Tab Alleman
  • Marked as answer by pulsmartin on Dec 31, 2012 05:11 PM