CODEWITHSUNDEEP
sqlsql-serverutf-8
Mukhamedali Zhadigerov
Mukhamedali Zhadigerov

Reputation: 861

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: ??????

Upvotes: 2

Views: 1939

Answers (1)

Ajay2707
Ajay2707

Reputation: 5798

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'ничего не поделаешь')

Upvotes: 6

Related Questions