November 28, 2011 at 9:35 am
Hi,
can any one tell me why its showing "incorrect synatx near the keyword replace" in sybase database
when i ran the same below query in sql server 2005 for me i got the output with out any error like
declare @text nvarchar(100)
set @text = '1,2,3,4'
select @text = ''''+ replace(@text,',',''',''')+''''
select @text
output as -- 1','2','3','4
but we are using sybase database,i am getting error in the replace;can anyone correct it
November 28, 2011 at 10:12 am
Hi,
Firstly, a SQL Server forum probably isn't the best place to get help on Sybase. Also I'm not really sure on the etiquette of answering these questions, but in the aim of being helpful and having been left to struggle in the same situation, I'll try and help...
Secondly, if you're using SYbase ASE 12, then I don't think there's one function:
I've built this one for you, using a combination of functions:
STUFF('abcdefg',PATINDEX('%def%','cdefghi'), CHAR_LENGTH('def'), 'XXX')
This is where you can get a succinct explanation of what each of the functions does:
http://manuals.sybase.com/onlinebooks/group-as/asg1250e/sqlug/@Generic__BookTextView/31275;pt=30441
Hope this helps..
Cheers,
Jim.
November 28, 2011 at 10:26 am
Actually,what i am trying to do here is ..option to select multi select option for @as_firmselection parameter...in this drop down list i am having 3 charachters
WCM,WBNA,WBFS
Select @as_firmSelection ='''' + REPLACE(@as_firmSelection,',',''',''') + '''' --11182011_1
SET @bankFusiCode =''
IF 'WCM' IN (@as_firmSelection)
BEGIN
Select @bankFusiCode = '''' + bankFusiCode + ''''
from frmmaster
where oldfirmcode = 'WCM'
END
IF 'WBNA' IN (@as_firmSelection)
BEGIN
Select @bankFusiCode = @bankFusiCode + ''',''' + bankFusiCode + ''''
from frmmaster
where oldfirmcode = 'WBNA'
END
IF 'WBFS' IN (@as_firmSelection)
BEGIN
Select @bankFusiCode = @bankFusiCode + ''',''' + bankFusiCode + ''''
from frmmaster
where oldfirmcode = 'WBFS'
END
i am getting error at the replace function;can this stuff will work here in sybase
November 29, 2011 at 3:50 am
declare @text nvarchar(100)
set @text = '1,2,3,4'
select @text = replace(@text,',',''',''')
select @text
gives output as -- 1','2','3','4
November 29, 2011 at 5:18 am
It seems like the issue you're having is that the REPLACE function won't work in Sybase.. So why can't you try replacing the REPLACE function with this one:
STUFF('abcdefg',PATINDEX('%def%','cdefghi'), CHAR_LENGTH('def'), 'XXX')
Or without sample data:
STUFF(<ORIGINAL_STRING>,PATINDEX(<SEARCH_TEXT>,<ORIGINAL_STRING>), CHAR_LENGTH(<SEARCH_TEXT>), <REPLACE_TEXT>)
November 29, 2011 at 5:43 am
direct from sybase BOL http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.blocks/html/blocks/blocks213.htm
syntax is exactly the same as the SQL function
REPLACE('abcdefg','cde','xxx')
gives abxxxfg
November 29, 2011 at 6:05 am
You're right Anthony, however it depends on which version of Sybase you're using.. For instance we've got ASE 12.0 and it doesn't support REPLACE...
On the other hand we also have an Advantage Database, which is also produced by Sybase and REPLACE does work.. I believe REPLACE is also supported by ASA8...
November 29, 2011 at 6:23 am
back to the OP to detail which version of Sybase they are running then, replace was introduced in ASE 12.5.2
November 29, 2011 at 6:29 am
November 29, 2011 at 8:27 am
Hi jim,
I am about to try your stuff function instead of replace function in my code,can you confirm my cod eweather its correct or not
STUFF(<ORIGINAL_STRING>,PATINDEX(<SEARCH_TEXT>,<ORIGINAL_STRING>), CHAR_LENGTH(<SEARCH_TEXT>), <REPLACE_TEXT>)
STUFF(@as_firmSelection,PATINDEX(',','----'), CHAR_LENGTH(','), ''',''') in the place of this original string i am not understanding what to use can anyone help me in this code...
Select @as_firmSelection ='''' + REPLACE(@as_firmSelection,',',''',''') + '''' --this is my repalce function
November 29, 2011 at 9:12 am
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 🙁
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply