Styjun

Watching something be written to a file live with tail

Languages that we cannot (dis)prove to be Context-Free

Which models of the Boeing 737 are still in production?

Accidentally leaked the solution to an assignment, what to do now? (I'm the prof)

Why do I get two different answers for this counting problem?

Why doesn't Newton's third law mean a person bounces back to where they started when they hit the ground?

Approximately how much travel time was saved by the opening of the Suez Canal in 1869?

Is a tag line useful on a cover?

Adding span tags within wp_list_pages list items

Font hinting is lost in Chrome-like browsers (for some languages )

Arthur Somervell: 1000 Exercises - Meaning of this notation

How to find program name(s) of an installed package?

Prove that NP is closed under karp reduction?

Why are electrically insulating heatsinks so rare? Is it just cost?

What do three bars across the stem of a note mean?

Collect Fourier series terms

How did the USSR manage to innovate in an environment characterized by government censorship and high bureaucracy?

Test whether all array elements are factors of a number

How does strength of boric acid solution increase in presence of salicylic acid?

How can I make my BBEG immortal short of making them a Lich or Vampire?

What typically incentivizes a professor to change jobs to a lower ranking university?

Schoenfled Residua test shows proportionality hazard assumptions holds but Kaplan-Meier plots intersect

Can I ask the recruiters in my resume to put the reason why I am rejected?

Pattern match does not work in bash script

Convert multiple row from one row based on specific character

How to concatenate text from multiple rows into a single text string in SQL server?SQL update from one Table to another based on a ID matchCan I concatenate multiple MySQL rows into one field?Inserting multiple rows in a single SQL query?What are the options for storing hierarchical data in a relational database?How to write UPDATE SQL with Table alias in SQL Server 2008?SQL Server Operating system error 5: “5(Access is denied.)”The data types varchar and datetimeoffset are incompatible in the add operatorSQL Replace and where CAST(PromotionRuleData as NVARCHAR(MAX))SQL Server 2008 to 2012 Message 402 The data types datetime and time are incompatible in the add operator

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;

1

I am trying to convert from one row to multiple row based on specific character
I copied a script from someone which was working with his/her example but not working for me which is as below

;WITH tmp(NOTEINDX, DataItem, TXTFIELD) AS
(
 SELECT
 NOTEINDX,

 LEFT(TXTFIELD, CHARINDEX('#', TXTFIELD + '#') - 1),
 STUFF(TXTFIELD, 1, CHARINDEX('#', TXTFIELD + '#'), '')
 FROM SY03900
 UNION all

 SELECT
 NOTEINDX,

 LEFT(TXTFIELD, CHARINDEX('#', TXTFIELD + '#') - 1),
 STUFF(TXTFIELD, 1, CHARINDEX(',', TXTFIELD + '#'), '')
 FROM tmp
 WHERE
 TXTFIELD > ''
)

SELECT
 NOTEINDX,

 DataItem
FROM tmp
ORDER BY NOTEINDX

However I am getting below error

Msg 402, Level 16, State 1, Line 6
The data types text and varchar are incompatible in the add operator.

I'm using SQL Server 2008
Thanks,
Hatim

sql sql-server

share|improve this question

edited Mar 21 at 23:45

Kingsley

3,51231428

asked Mar 21 at 23:42

Hatim Hatim

61

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  You need to cast the text field to varchar() fields, stuff() won't work on text fields
  
  – Sparky
  Mar 21 at 23:52
  

  
  
  
  

add a comment | 

1

I am trying to convert from one row to multiple row based on specific character
I copied a script from someone which was working with his/her example but not working for me which is as below

;WITH tmp(NOTEINDX, DataItem, TXTFIELD) AS
(
 SELECT
 NOTEINDX,

 LEFT(TXTFIELD, CHARINDEX('#', TXTFIELD + '#') - 1),
 STUFF(TXTFIELD, 1, CHARINDEX('#', TXTFIELD + '#'), '')
 FROM SY03900
 UNION all

 SELECT
 NOTEINDX,

 LEFT(TXTFIELD, CHARINDEX('#', TXTFIELD + '#') - 1),
 STUFF(TXTFIELD, 1, CHARINDEX(',', TXTFIELD + '#'), '')
 FROM tmp
 WHERE
 TXTFIELD > ''
)

SELECT
 NOTEINDX,

 DataItem
FROM tmp
ORDER BY NOTEINDX

However I am getting below error

Msg 402, Level 16, State 1, Line 6
The data types text and varchar are incompatible in the add operator.

I'm using SQL Server 2008
Thanks,
Hatim

sql sql-server

share|improve this question

edited Mar 21 at 23:45

Kingsley

3,51231428

asked Mar 21 at 23:42

Hatim Hatim

61

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  You need to cast the text field to varchar() fields, stuff() won't work on text fields
  
  – Sparky
  Mar 21 at 23:52
  

  
  
  
  

add a comment | 

I am trying to convert from one row to multiple row based on specific character
I copied a script from someone which was working with his/her example but not working for me which is as below

;WITH tmp(NOTEINDX, DataItem, TXTFIELD) AS
(
 SELECT
 NOTEINDX,

 LEFT(TXTFIELD, CHARINDEX('#', TXTFIELD + '#') - 1),
 STUFF(TXTFIELD, 1, CHARINDEX('#', TXTFIELD + '#'), '')
 FROM SY03900
 UNION all

 SELECT
 NOTEINDX,

 LEFT(TXTFIELD, CHARINDEX('#', TXTFIELD + '#') - 1),
 STUFF(TXTFIELD, 1, CHARINDEX(',', TXTFIELD + '#'), '')
 FROM tmp
 WHERE
 TXTFIELD > ''
)

SELECT
 NOTEINDX,

 DataItem
FROM tmp
ORDER BY NOTEINDX

However I am getting below error

Msg 402, Level 16, State 1, Line 6
The data types text and varchar are incompatible in the add operator.

I'm using SQL Server 2008
Thanks,
Hatim

sql sql-server

share|improve this question

edited Mar 21 at 23:45

Kingsley

3,51231428

asked Mar 21 at 23:42

Hatim Hatim

61

;WITH tmp(NOTEINDX, DataItem, TXTFIELD) AS
(
 SELECT
 NOTEINDX,

 LEFT(TXTFIELD, CHARINDEX('#', TXTFIELD + '#') - 1),
 STUFF(TXTFIELD, 1, CHARINDEX('#', TXTFIELD + '#'), '')
 FROM SY03900
 UNION all

 SELECT
 NOTEINDX,

 LEFT(TXTFIELD, CHARINDEX('#', TXTFIELD + '#') - 1),
 STUFF(TXTFIELD, 1, CHARINDEX(',', TXTFIELD + '#'), '')
 FROM tmp
 WHERE
 TXTFIELD > ''
)

SELECT
 NOTEINDX,

 DataItem
FROM tmp
ORDER BY NOTEINDX

Msg 402, Level 16, State 1, Line 6
The data types text and varchar are incompatible in the add operator.

sql sql-server

sql sql-server

share|improve this question

edited Mar 21 at 23:45

Kingsley

3,51231428

asked Mar 21 at 23:42

Hatim Hatim

61

share|improve this question

edited Mar 21 at 23:45

Kingsley

3,51231428

asked Mar 21 at 23:42

Hatim Hatim

61

edited Mar 21 at 23:45

Kingsley

3,51231428

edited Mar 21 at 23:45

Kingsley

3,51231428

asked Mar 21 at 23:42

Hatim Hatim

61

asked Mar 21 at 23:42

Hatim Hatim

61