CODEWITHSUNDEEP
sql-server
dbjoe
dbjoe

Reputation: 65

SQL Server UDF Function to replace characters in column

I am trying to create a function in SQL server that takes a column name as a parameter this column contains first names of people, I manually inserted these names. Any name that contains a special character such as ' as in O'Brian I inserted the value as O$6Brian the $6 representing ' . The same for Jack & Jill the & I inserted as $7 so in the database this looks like Jack $7 Jill.

so when someone passes the name of the Names column which in my case is called fname into the function I would like the function to replace the $6 and $7 with the appropriate character.

I know this can be done using in the front end app but I just want to learn some more SQL hence why I am asking I have the following so far which does not work.

create function fn_convertDBChars(@stringtoconvert nvarchar)
returns nvarchar(20)
as
begin
    declare @Return nvarchar(20)

    select 
        @Return = case @stringtoconvert
                     when '$6' then REPLACE(@stringtoconvert, '$7', '&')
                  end

    return @Return
end;

I would be grateful for any advice.

Upvotes: 0

Views: 1240

Answers (2)

Kashyap MNVL
Kashyap MNVL

Reputation: 603

You were correct with most part of your function, except you should be using LIKE operator to match the patterns. You can replace $6 with single-quotes and $7 with & symbol in a user-defined function like this. Of course, you would be using the REPLACE function internally. Here is what your function would look like.

create function fn_convertDBChars(@stringtoconvert NVARCHAR(20))
    returns nvarchar(20)
as
begin
    declare @Return nvarchar(20)
    select @Return = CASE WHEN @stringtoconvert LIKE '%$6%' then REPLACE(@stringtoconvert, '$6', '''')
    WHEN @stringtoconvert LIKE '%$7%' THEN REPLACE(@stringtoconvert,'$7','&')
    ELSE @stringtoconvert END 
return @Return
end;


SELECT dbo.fn_convertDBChars('Jack $7 Jill') -- Returns Jack & Jill
SELECT dbo.fn_convertDBChars('O''Brian')  -- Returns O'Brian
SELECT dbo.fn_convertDBChars(fname) from dbo.table_name  -- This is how you would call your function on the column.

Notice the escape character used here to escape single-quotes. You can escape that by using two occurrences of single-quotes.

Upvotes: 1

George Menoutis
George Menoutis

Reputation: 7250

Just use the built-in function REPLACE like below:

set @Return = REPLACE(@stringtoconvert, '$7', '&') 
where @stringtoconvert like '%$7%'

Upvotes: 1

Related Questions