6

I am unable to use sybase replace function to replace data. Basically what i need is to update all the occurrences of 'abc' and change with 'zzz'.] table_clmn is data type text I am using the following sql --

update  table
set table_clmn = replace(table_clmn , 'abc', 'zzz') WHere id in (1, 2)

I get the following error

Incorrect syntax near the keyword 'replace'.

jpaugh
  • 6,634
  • 4
  • 38
  • 90
user841293
  • 67
  • 1
  • 1
  • 8
  • Sybase is not a database. You'll need to specify what Sybase product, and version you are using (ASE, SqlAnywhere, IQ,etc) – Mike Gardner Feb 13 '15 at 20:32

1 Answers1

8

Assuming you're using Sybase ASE, you need to use the built-in function 'str_replace()' instead of 'replace()'. The 'replace' function can only be used for changing the default constraint on a table column.

Pang
  • 9,564
  • 146
  • 81
  • 122
RobV
  • 2,263
  • 1
  • 11
  • 7
  • Thanks Rob , yes i tried that. so this is what i am doing UPDATE tableName set clnName = str_replace(convert(varchar(16384),clnName), 'ABC', 'XYZ') WHERE ClnId = 1, one issue i see here is even though no record is updated the result set says 1 record updated , probably thats the outcome of the convert function.Also do u see any harm is converting text datatype to varchar using convert function?Since the column 'clnName' is text data type i have to use convert function to change it to varchar before i can use the replace function – user841293 Feb 16 '15 at 18:34
  • Another issue i notice is : column of data type text can hold larger size than varchar so the convert function i am using is resulting in truncated data...any tips on how to get this working?? – user841293 Feb 17 '15 at 02:08
  • 1
    A 'text' can contain up to 2GB, varchar cannot contain more than 16384 bytes (for a variable) or even less for a table column. So these are fundamentally incompatible if there is more than 16384 bytes of data in the text value. Since str_replace() operates only on varchar, this may not be a feasible solution. Now if you are running ASE 15.7, you can use the charindex() and substring() functions which operate on LOB values too. But you can replace only on string at a time so you will need to write a loop and perform multiple passes until all strings are replaced. – RobV Feb 17 '15 at 21:51
  • On the first issue: if it sayd 1 row is updated, then it *did* update 1 row. It may be there were no strings 'ABC' to be replaced, but your update query will perform the update anyway. To avoid that, add predicate "AND charindex('ABC',CInName) > 0" – RobV Feb 17 '15 at 21:56
  • Dunno 'bout the OP, but this helped me. Thanks! (I forgot I wasn't using T-SQL!) – jpaugh Oct 11 '16 at 14:11