sqlsql-serverutf-8

SQL Server function returns question marks instead of real result


I have this function which retrieves first word from String:

CREATE FUNCTION dbo.FIRST_WORD(@value nvarchar(1000))
RETURNS nvarchar(1000)
AS
BEGIN
RETURN CASE CHARINDEX(' ', @value, 1)
       WHEN 0
         THEN @value
       ELSE SUBSTRING(@value, 1, CHARINDEX(' ', @value, 1) - 1) END
 END
GO 

The problem is the data in my table is in non-ASCII format, so when i pass some value to that function, i get question marks instead of a result:

SELECT dbo.FIRST_WORD('ничего не поделаешь')

returns: ??????

But if i pass ASCII characters, for example:

SELECT dbo.FIRST_WORD('hello world')

it returns: hello as expected.

I tried to add N before the argument, but it didn't help:

SELECT dbo.FIRST_WORD(N'ничего не поделаешь')

still returns: ??????


Solution

  • There are some issues

    1. Missing Select Keyword
    2. Missing N while sending Unicode data.
    3. The return statement will be proper

    This is you need :

    Create FUNCTION dbo.FIRST_WORD (@value nvarchar(max))
     RETURNS nvarchar(1000)
    AS
    BEGIN
    
     Return (Select CASE CHARINDEX(' ', @value, 1)
       WHEN 0
         THEN @value
       ELSE SUBSTRING(@value, 1, CHARINDEX(' ', @value, 1) - 1) END)
    
     END
    GO 
    
    Select dbo.FIRST_WORD(N'ничего не поделаешь')