locked
Arabic language not display when i make search by arabic language why RRS feed

  • Question

  • <p>Hi guys in my database i have hrdatabase has table Employee</p><p>Table Employee has field EmployeeName nvarchar(50)</p><p>my sql server support arabic language but i dont know how to handel query to accept arabic</p><p>in query analyzer i write</p><span style="color:#0000ff;font-size:small;"><span style="color:#0000ff;font-size:small;"> <p>select * from Employee where EmployeeName='احمد'</p><p>Not give me any result</p><p>i modify query as following</p><p>select * from Employee where EmployeeName=N'احمد'</p><p>it work and give me result meaning my sql support arabic</p><p>my proplem in this stored procedure</p><p>it is dynamic stored procedure and have more parameter but i dont know how to modify to accept arabic</p><p></p><span style="color:#0000ff;font-size:small;"><span style="color:#0000ff;font-size:small;"> <p></p><p><span style="color:#ff0000;font-size:small;"><span style="color:#ff0000;font-size:small;"></span></span><span style="color:#ff0000;font-size:small;"><span style="color:#ff0000;font-size:small;"></span></span><span style="color:#ff0000;font-size:small;"><span style="color:#ff0000;font-size:small;"></span></span><p></p><span style="color:#ff0000;font-size:small;"><span style="color:#ff0000;font-size:small;"></span></span><span style="color:#ff0000;font-size:small;"><span style="color:#ff0000;font-size:small;"></span></span><span style="color:#ff0000;font-size:small;"><span style="color:#ff0000;font-size:small;"></span></span></p></span></span></span></span><span class="t-marker"></span>
    • Edited by Kalman Toth Sunday, September 7, 2014 4:44 PM Spelling
    Friday, September 5, 2014 8:21 PM

Answers

  • Let me try this again.  The correct code for your stored procedure should be

    Create Procedure up_EmployeeSelect
    @EmployeeName      nvarchar(50)  
    AS  
    Declare @SQLQuery as nvarchar(2000)
    SET @SQLQuery =N'SELECT * from Employee Where (1=1)'  
    If @EmployeeName  <> N''
    Set @SQLQuery = @SQLQuery + N' AND (EmployeeName Like N''%' + @EmployeeName + N'%'')'
    -- Select @SQLQuery
    Exec (@SQLquery)

    Please note I changed the name of your stored procedure to up_EmployeeSelect.  Naming it sp_EmployeeSelect was not the cause of your problem, but it is bad practice to name any stored procedure you write as sp_<whatever>.  sp_ as a prefix should be reserved for system stored procedures released by Microsoft.

    Tom

    • Proposed as answer by Kalman Toth Sunday, September 7, 2014 4:45 PM
    • Marked as answer by Kalman Toth Wednesday, September 17, 2014 1:08 PM
    Saturday, September 6, 2014 6:48 AM
  • You should of course use sp_executesql:

    Create Procedure up_EmployeeSelect
    @EmployeeName      nvarchar(50) 
    AS 
    Declare @SQLQuery as nvarchar(2000)
    SET @SQLQuery =N'SELECT * from Employee Where (1=1)' 
    If @EmployeeName  <> N''
    Set @SQLQuery = @SQLQuery + N' AND EmployeeName LIKE N''%'' + @EmployeeName + N''%'''
    EXEC sp_executesql @SQLQuery, N'@EmployeeName nvarchar(50)', @EmployeeName

    Or skip the dynamic SQL:

    Create Procedure up_EmployeeSelect
    @EmployeeName      nvarchar(50) 
    AS 
    SELECT *
    FROM   Employee
    WHERE  (EmployeeName LIKE N'%' + @EmployeeName + N'%' OR @EmployeeName <> N'')
    OPTION (RECOMPILE)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Kalman Toth Wednesday, September 17, 2014 1:07 PM
    Sunday, September 7, 2014 9:09 PM

All replies

  • Hi guys

    I have database hr has table Employee this table  has field EmployeeName

    EmployeeName  nvarchar(50)

    when i write in query analyzer :

    select ^ from Employee where EmployeeName='احمد'

    not give me any result

    but when i write

    select * from Employee where EmployeeName=N'احمد'

    it give me result

    meaning it support arabic

    but i have stored procedure not accept arabic and i dont know how to handel it to accept search by EmployeeName

    CREATE Procedure sp_EmployeeSelect
    @EmployeeName      nvarchar(50)  
    AS  
    Declare @SQLQuery as nvarchar(2000)
    SET @SQLQuery ='SELECT * from Employee Where (1=1)'  
    If @EmployeeName  <>''
    Set @SQLQuery = @SQLQuery + ' AND (EmployeeName  LIKE ''%'+@EmployeeName +'%'') '
    Exec (@SQLQuery)

    what is the proplem in this stored procedure and how to solve it

    please help me

    Friday, September 5, 2014 9:01 PM
  • It's somewhat hard to read your question because of the xml formatting characters, but I think your question is

    Hi guys in my database i have hrdatabase has table Employee
    Table Employee has field EmployeeName nvarchar(50)
    my sql server support arabic language but i dont know how to handel query to accept arabic
    in query analyzer i write
    select * from Employee where EmployeeName='احمد'
    Not give me any result
    i modify query as following
    select * from Employee where EmployeeName=N'احمد'
    it work and give me result meaning my sql support arabic
    my proplem in this stored procedure
    it is dynamic stored procedure and have more parameter but i dont know how to modify to accept arabic

    The answer is that when you have 16 bit UNICODE data you must always compare the results as unicode, store the data in unicode (that is, nvarchar, nchar, or ntext) columns and variables and when using literals, you must use unicode literals (that is use N'<the literal you want>', not '<the literal you want>'.  This is because whenever you implicitly or explicitly convert a unicode value to an eight bit value and there are characters in the unicode value that don't have a corresponding match in the 8 bit system, those values are converted to question marks.  So in

    Select * From Employee Where EmployeeName = 'احمد'

    the 'احمد' are 16 bit unicode characters, but you are putting them into an 8 bit literal, so those characters get converted to question marks and that statement is equivalent to

    Select * From Employee Where EmployeeName = '????'

    and you don't get any rows back because you don't have any emplyees whose name is ????.

    So when you are writing and calling your stored procedure, every thing that can contain an unicode value must be a unicode literal, column, or variable.  That means all such literals must be of the form N'<something', your columns, variables, and parameters that can contain unicode data need to be nvarchar or nchar, any unicode values passed to that procedure must be nvarchar, nchar, or a unicode literal.

    Tom 

    • Proposed as answer by Russ Loski Saturday, September 6, 2014 8:37 PM
    Friday, September 5, 2014 9:25 PM
  • TL:DR>

    Don't use VARCHAR for Unicode. Use NVARCHAR. If you do not explicitly cast as NVARCHAR it will be treated as VARCHAR. You can cast as NVARCHAR by prefixing the string with an N, N'This is a nvarchar', or by using a pre-cast variable, DECLARE @employeeName NVARCHAR(30).


    Duplicate of http://social.msdn.microsoft.com/Forums/sqlserver/en-US/cedc6488-d659-4bdf-b123-4e8b4bc183e7 (cedc6488-d659-4bdf-b123-4e8b4bc183e7)
    Friday, September 5, 2014 9:35 PM
  • CREATE Procedure sp_EmployeeSelect
    @EmployeeName      nvarchar(50)  
    AS  
    Declare @SQLQuery as nvarchar(2000)
    SET @SQLQuery =N'SELECT * from Employee Where (1=1)'  
    If @EmployeeName  <> N''
    Set @SQLQuery = @SQLQuery + ' AND (EmployeeName  LIKE 'N'%'+@EmployeeName +'%'') '
    Exec (@SQLQuery)

    I think this is the solution.
    Friday, September 5, 2014 9:49 PM
  • Thank you for reply

    i test solution above but not work are there are any solution

    Saturday, September 6, 2014 4:49 AM
  • Let me try this again.  The correct code for your stored procedure should be

    Create Procedure up_EmployeeSelect
    @EmployeeName      nvarchar(50)  
    AS  
    Declare @SQLQuery as nvarchar(2000)
    SET @SQLQuery =N'SELECT * from Employee Where (1=1)'  
    If @EmployeeName  <> N''
    Set @SQLQuery = @SQLQuery + N' AND (EmployeeName Like N''%' + @EmployeeName + N'%'')'
    -- Select @SQLQuery
    Exec (@SQLquery)

    Please note I changed the name of your stored procedure to up_EmployeeSelect.  Naming it sp_EmployeeSelect was not the cause of your problem, but it is bad practice to name any stored procedure you write as sp_<whatever>.  sp_ as a prefix should be reserved for system stored procedures released by Microsoft.

    Tom

    • Proposed as answer by Kalman Toth Sunday, September 7, 2014 4:45 PM
    • Marked as answer by Kalman Toth Wednesday, September 17, 2014 1:08 PM
    Saturday, September 6, 2014 6:48 AM
  • You should of course use sp_executesql:

    Create Procedure up_EmployeeSelect
    @EmployeeName      nvarchar(50) 
    AS 
    Declare @SQLQuery as nvarchar(2000)
    SET @SQLQuery =N'SELECT * from Employee Where (1=1)' 
    If @EmployeeName  <> N''
    Set @SQLQuery = @SQLQuery + N' AND EmployeeName LIKE N''%'' + @EmployeeName + N''%'''
    EXEC sp_executesql @SQLQuery, N'@EmployeeName nvarchar(50)', @EmployeeName

    Or skip the dynamic SQL:

    Create Procedure up_EmployeeSelect
    @EmployeeName      nvarchar(50) 
    AS 
    SELECT *
    FROM   Employee
    WHERE  (EmployeeName LIKE N'%' + @EmployeeName + N'%' OR @EmployeeName <> N'')
    OPTION (RECOMPILE)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Kalman Toth Wednesday, September 17, 2014 1:07 PM
    Sunday, September 7, 2014 9:09 PM
  • >select * from Employee where EmployeeName='احمد'

    Use the N prefix for UNICODE string literals:

    select * from Employee where EmployeeName=N'احمد'



    Kalman Toth Database & OLAP Architect SQL Server 2014 Database Design
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012



    • Edited by Kalman Toth Wednesday, September 17, 2014 1:10 PM
    Wednesday, September 17, 2014 1:09 PM