Issue with replace function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Issue with replace function

Author

Topic

atlachar123456
Starting Member

33 Posts

Posted - 2011-11-23 : 11:51:08

here i have a requiremnet to select multiple option for @selection user parameter.

declare @selection varchar(20)
declare @bank varchar(20)

Select @Selection = REPLACE(@Selection,',',''',''')
SET @bank=''
IF 'abc' in (@Selection) -- If abc is in @selection then we are taking bank
BEGIN
Select @bank = '''' + bank + '''' --"abc","def"--bank is taking abc and def as separate string with double cotes separated by comma.we can expect output as abc,def
from firm
where oldfirmcode = 'abc'
END

IF 'def' in (@Selection)
BEGIN
Select @bank = @bank + '','' + bank + '''' -- In the second step taking bankFusiCode and adding to above variables
from firm
where oldfirmcode = 'def'
END

IF 'ghi' in (@Selection)
BEGIN
Select @bank = @bank + '','' + bank + ''''--In the bank group for these values if we have data,then we get the output.
from firm
where oldfirmcode = 'ghi'
END

when i am trying to execute this query i am getting error at replace function and
i) in correct syntax near replace function
2) A select statemnet that assigns a value to a variable must not be combined with data retrieval operations

can anyone correct my query ...

atlaaaaaaaa

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-11-23 : 12:10:10

Looks like you have too many single-quotes. Try changing the '','' to ','

johntech
Yak Posting Veteran

51 Posts

Posted - 2011-11-23 : 13:12:03

The Replace function in SQL is used to update the content of a string. The function call is REPLACE() for MySQL, Oracle, and SQL Server. The syntax of the Replace function is:

Replace(str1, str2, str3): In str1, find where str2 occurs, and replace it with str3.

Assume we have the following table:

Table Geography
region_name store_name
East Boston
East New York
West Los Angeles
West San Diego

If we apply the following Replace function:

SELECT REPLACE(region_name, 'ast', 'astern')
FROM Geography;

Result:
region_name
Eastern
Eastern
West
West

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-23 : 13:30:00

IF 'abc' in (@Selection)

What do you expect this to do?
I have a suspicion it won't do what you want it to.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

atlachar123456
Starting Member

33 Posts

Posted - 2011-11-23 : 14:47:39

quote:
Originally posted by johntech

The Replace function in SQL is used to update the content of a string. The function call is REPLACE() for MySQL, Oracle, and SQL Server. The syntax of the Replace function is:

Replace(str1, str2, str3): In str1, find where str2 occurs, and replace it with str3.

Assume we have the following table:

Table Geography
region_name store_name
East Boston
East New York
West Los Angeles
West San Diego

If we apply the following Replace function:

SELECT REPLACE(region_name, 'ast', 'astern')
FROM Geography;

Result:
region_name
Eastern
Eastern
West
West

atlaaaaaaaa

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-24 : 06:11:53

Following on from my above post I suspect that instead of
Select @Selection = REPLACE(@Selection,',',''',''')
IF 'abc' in (@Selection)

you might want
if ',' + @Selection + ',' like '%,abc,%'

Depends on what you are trying to do though.
As to the error - can't see anything wrong with the replace but
Select @bank = @bank + '','' + bank + ''''
s.b. Select @bank = @bank + ''',''' + bank + ''''
or Select @bank = @bank + ',' + bank + ''''
but still looks odd - not sure whether you are trying to match quotes in the string or not.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources