SQL 2000 Concatenate text fields within a view

• neilb-685126

  SSC Rookie

  Points: 45

  More actions

  March 25, 2008 at 2:15 pm

  #204812

  I'm using sql2000.

  I have a table with 4 text fields which need to be referenced in a view.

  The fields are called : Overview, description, ingredients, usage

  What I want to do is the following:

  create view MyView

  select

  productid,

  overview + '

  ' + description + '

  ' + ingredients + '

  ' + usage as LongDescription

  from

  myTable

  Then, in my code I would write:

  select productid,LongDescription from MyView where productid=123

  The data in the fields is > 8000 chars so I can't cast the data as varchar(8000)

  Anyone have any other ideas??

  I was thinking about creating a function, but I'm not 100% sure how??

• Loner

  SSC-Insane

  Points: 21279

  More actions

  March 26, 2008 at 12:54 pm

  #794288

  Can you cast it as text?

• Jeff Moden

  SSC Guru

  Points: 1004591

  More actions

  Follow

  August 26, 2008 at 9:53 am

  #863241

  Even if you concatenate and convert to text using something like...

  [font="Courier New"]SELECT CAST('1'+'2'+'3' AS TEXT)[/font]

  ...you still won't be able to see more than 8192 characters in a single column, view or not... please see the following article for an outstanding bit of information on the TEXT datatype.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)