Creating SQL Server function with REPLACE function within

Question

I'm trying to create function that takes data from table, replaces certain characters and returns replaced data. Function will be executed when called for.

CREATE FUNCTION convert_lat(@rawtxt NVARCHAR)
RETURNS NVARCHAR(50)
AS
BEGIN
    DECLARE @raw_mon NVARCHAR(50);

    SELECT @raw_mon = REPLACE (r.mongol, N'ф', 'f')
    FROM Connection.dbo.raw r
    WHERE r.mongol = @rawtxt

    RETURN @raw_mon;
END;
GO

UPDATE [Connection].[dbo].[raw] 
SET [mongol] = dbo.convert_lat(mongol) 
WHERE [mongol] LIKE N'ф%';

After I execute the UPDATE statement, characters that supposed to be replaced are not replaced but whole row becomes null.

Are you having issues? Glancing at it, it looks like accurate SQL code — Brad
– Brad, Commented May 9, 2018 at 12:52
Maybe you need a function that returns a table - TVF ? That code doesn't make much sense to me. — Cetin Basoz
– Cetin Basoz, Commented May 9, 2018 at 12:52
Try converting your scalar function to a table valued function, although it should work as it is now as long as your select retrieves just 1 row. — EzLo
– EzLo, Commented May 9, 2018 at 12:54
The main issue is that you didn't specify the size of your parameter. The default for a parameter is 1. ALWAYS specify the size. sqlblog.org/2009/10/09/… — Sean Lange
– Sean Lange, Commented May 9, 2018 at 13:08

hkravitz · Accepted Answer · 2018-05-09 12:56:22Z

2

As said in the comments, use ITVF function

CREATE FUNCTION convert_lat(@rawtxt nvarchar (400))
RETURNS TABLE AS RETURN
SELECT raw_mon = REPLACE (@rawtxt, N'ф', 'f')

GO

Use it:

SELECT R.* , F.raw_mon
FROM Connection.dbo.raw r
CROSS APPLY convert_lat (R.mongol) F

answered May 9, 2018 at 12:56

hkravitz

1,4051 gold badge12 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Xass1on Over a year ago

I will try it. Is it right way to include a lot of characters in : REPLACE (@rawtxt, N'ц,ч,ф', 'ts,ch,q')?

hkravitz Over a year ago

you can wrap each character with another replace, however it could be quiet long. if you need to replace a lot of characters it is best to work with a mapping table.

Yogesh Sharma · Accepted Answer · 2018-05-09 12:55:34Z

0

I would recommend to define function parameters with appropriate length

So,

CREATE FUNCTION convert_lat(@rawtxt nvarchar) -- Will accept 1 length default

It would be instead

CREATE FUNCTION convert_lat(@rawtxt nvarchar(255))

answered May 9, 2018 at 12:55

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

1 Comment

Joe Farrell Over a year ago

@Xass1on, this may fix your most immediate problem, but consider the answer from hkravitz as well. What it implies without really saying is that it doesn't seem to make sense for your function to contain a SELECT ... FROM Connection.dbo.Raw statement given that its only purpose appears to be to retrieve a string that you already have (in the @rawtxt parameter).

Collectives™ on Stack Overflow

Creating SQL Server function with REPLACE function within

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related