Issue with stuff function

• atlancharlo

  Mr or Mrs. 500

  Points: 554

  More actions

  November 29, 2011 at 8:54 am

  #390984

  Hi,

  Can any one corrrect my stuff function code..i want to get the output as 1','2','3','4;

  actually i have used replace function its working in sql server but not in sybase insteadof replace i am using stuff function in sybase.

  declare @text nvarchar(100)

  set @text = '1,2,3,4'

  select @text = ''''+ replace(@text,',',''',''')+''''

  select @text

  declare @text nvarchar(100)

  set @text = '1,2,3,4'

  select @text =''''+ STUFF(@text,PATINDEX(',',@text),CHAR_LENGTH(','),''',''') +''''

  select @text

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  November 29, 2011 at 12:06 pm

  #1414279

  From your original thread...http://www.sqlservercentral.com/Forums/Topic1212595-392-1.aspx

  OK,

  There's a problem... This function will only replace the very first instance of the pattern you are searching for:

  SELECT STUFF(@as_firmSelection,PATINDEX('%,%',@as_firmSelection), CHAR_LENGTH(@as_firmSelection), ''',''')

  Run the above and you'll see what I mean.

  So you're probably going to have to write some code to find out the location of each of the commas ( , ) and replace them with ( ',' )

  Sorry I couldn't be more help [Sad]

  The issue is not really with your code but that it will only find the first one.

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

• Manjuke

  SSChasing Mays

  Points: 629

  More actions

  November 30, 2011 at 1:14 am

  #1414473

  Hi,

  Isn't there a function called 'str_replace()' in SyBase, for text replacement functionality ??

  --Manjuke

  http://www.manjuke.com

  --------
  Manjuke
  http://www.manjuke.com