SQL function question

Question

I have an sql function and at the end RETURN @_result;.

My question is that if I put this

REPLACE(@_result, '&', 'eseses')
REPLACE(@_result, '-', 'vagyvagy')

before RETURN @_result; that is OK?

@_result returns a long string and in that string I want to replace & to eseses and - to vagyvagy.

Answer 1

yes that is okay in SQL Server

Answer 2

You need to put the following:

SET @_result = REPLACE(@_result, '&', 'eseses')
SET @_result = REPLACE(@_result, '-', 'vagyvagy')

Answer 3

Yes, the idea is ok but your code isn't syntactically sound. You'd need to do:

SET @_result = REPLACE(@_result, '&', 'eseses')
SET @_result = REPLACE(@_result, '-', 'vagyvagy')

RETURN @_result

You may know this but I wanted to ensure that you're clear on that in case you are trying it and it's not working. (Some DB implementations may allow REPLACE w/o the set, but I know of none.)

Answer 4

You need the SET before the REPLACE in SQL 2008.

When I run the following

DECLARE @_result as varchar(30)

SET @_result = 'Hello-There & How are you?'

REPLACE(@_result, '&', 'eseses')
REPLACE(@_result, '-', 'vagyvagy')

SELECT @_result

I get the following error

Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'REPLACE'.

By updating it to the following, the error went away and everything is set correctly

DECLARE @_result as varchar(30)

SET @_result = 'Hello-There & How are you?'

SET @_result = REPLACE(@_result, '&', 'eseses')
SET @_result = REPLACE(@_result, '-', 'vagyvagy')

SELECT @_result