Like Operation Returns No Rows on Nvarchar Column Filter If the Column Data Start with Numeric

Like Operator does not work

Well, I found the error.

The content was uploaded by a client (a spammer) and when look the ascii of each character, I found out that the client put the content on unicode characters that are similar.

So "Paypal" wasn't "Paypal" like expected, here is the result of the Paypal word with the comparison with expected result:

LIKE operator, N and % SQL Server doesn't work on nvarchar column

You don't need to use N prefix in the WHERE clause since your variable is already nvarchar, and you are passing a variable not a literal string.

Here is an example:

CREATE TABLE People
(
  ID INT,
  Name NVARCHAR(45)
);

INSERT INTO People VALUES
(1, N'حسام'),
(2, N'حسان'),
(3, N'حليم');

DECLARE @Name NVARCHAR(45) = N'حس';--You need to use N prefix when you pass the string literal

SELECT *
FROM People
WHERE Name LIKE @Name + '%'; --You can use it here when you pass string literal, but since you are passing a variable, you don't need N here

Live demo

You may have seen Transact-SQL code that passes strings around using an N prefix. This denotes that the subsequent string is in Unicode (the N actually stands for National language character set). Which means that you are passing an NCHAR, NVARCHAR or NTEXT value, as opposed to CHAR, VARCHAR or TEXT.

From docs

Prefix Unicode character string constants with the letter N. Without the N prefix, the string is converted to the default code page of the database. This default code page may not recognize certain characters.

---

To answer your question in the comment with a simple answer, you are using the wrong datatype, so ALTER the stored procedure and change the datatype of your parameter from VARCHAR to NVARCHAR.

---

UPDATE:

Since you are using an SP, you can create your SP (according to your comment) as

CREATE PROCEDURE MyProc
(
  @Var NVARCHAR(45)
)
AS 
BEGIN
  SELECT *
  FROM People
  WHERE Name LIKE ISNULL(@Var, Name) + '%';
  --Using ISNULL() will return all rows if you pass NULL to the stored procedure
END

and call it as

EXEC MyProc N'حس'; --If you don't use N prefix then you are pass a varchar string

If you see, you need to use the N prefix when you pass literal string to your SP not inside the SP or the WHERE clause neither.

Demo for the SP

Between clause on Array of numerical values in row for JSON type column

You may use an APPLY operator and an OPENJSON() call to parse the stored JSON and apply the appropriate WHERE clause:

SELECT * 
FROM (VALUES (N'[{"key": 12}, {"key": 13}, {"key": 19}]')) v (JsonData)
CROSS APPLY OPENJSON(v.JsonData) WITH ([key] int '$.key') j
WHERE j.[key] BETWEEN 5 AND 12

How to extract alpha or numbers from a string within a SELECT statement WITHOUT a function call

How to extract alpha or numbers from a string within a SELECT statement WITHOUT a function call?

You can't, because simple string operations like REPLACE() are also function calls. And, user-defined functions are compiled by SQL Server. They perform pretty well.

But your problem is not the function's performance itself, but rather how often you must use it. You knew that.

Here's a possible way to speed up your postcode-grinding task: put persisted computed columns on your table. You can even index them.

Here's how to do that.

1. Tell SQL Server to use schemabinding with your stored function. It needs to know that a table definition in your schema depends on the function. To do that add WITH SCHEMABINDING to your function definition.

   ...
   RETURNS NVARCHAR(MAX)
   WITH SCHEMABINDING
   AS
   BEGIN
   ...
   

2. Add two computed, persisted, columns to your table.

   ...
   ALTER TABLE postcode
          ADD letters   
          AS (dbo.fn_StripCharacters(postcode, '^A-Z'))
          PERSISTED;
    ALTER TABLE dbo.postcode
          ADD numbers
          AS (CAST(dbo.fn_StripCharacters(postcode, '^0-9') AS INT)) 
          PERSISTED;
   

3. You can put indexes on the computed columns if you need them.

   CREATE INDEX numbers ON postcode  (numbers DESC)
   

4. Now you can insert, update, or delete your non-computed columns as you wish. SQL Server evaluates your stored functions just once for each row as it is inserted or updated. Your functions still get evaluated, but not when you SELECT from your table.

5. And you can use the computed columns as you wish

   SELECT * FROM postcode ORDER BY numbers DESC
   

Here's a db<>fiddle demonstrating this.

Drop Columns in multiple tables that have Column Name similar to values in another Table

Check if this procedure works as expected.

Have a look at fiddle, (you'll find a link at bottom).

I've used a double dynamic query.

First one returns a list of comma separated of similar columns.

SELECT @columns = STUFF((SELECT ',' + COLUMN_NAME 
                      FROM INFORMATION_SCHEMA.COLUMNS
                      WHERE  (COLUMN_NAME LIKE '%DEF%' OR COLUMN_NAME LIKE '%GHI%' OR COLUMN_NAME LIKE '%JKL%' OR COLUMN_NAME LIKE '%MNO%') AND TABLE_NAME = 'TB1'
                      FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

Second query alter table by dropping columns:

ALTER TABLE TB1 DROP COLUMN DEF,DEF1,DEF2,GHI,GHI1,JKL,JKL1,MNO,MNO1

This is the function:

CREATE PROCEDURE dbo.DropColumns(@table_name NVARCHAR(128))
AS
BEGIN

    DECLARE @cmd NVARCHAR(MAX);
    DECLARE @colsOR NVARCHAR(MAX);
    DECLARE @cols NVARCHAR(MAX);

    -- filter used in WHERE clause (COLUMN_NAME LIKE '%REF%' OR ...)
    SET @colsOR = STUFF((SELECT ' OR ' + CONCAT('COLUMN_NAME LIKE ''%', CName, '%''') 
                         FROM TblMaster
                         WHERE FLAG = 1
                         FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 4, '');

    -- columns returned separated by comma DEF,DEF1,DEF2,GHI,GHI1,JKL,JKL1,MNO,MNO1
    SET @cmd = 'SELECT @columns = STUFF((SELECT '','' + COLUMN_NAME 
                      FROM INFORMATION_SCHEMA.COLUMNS
                      WHERE  (' + @colsOR + ') AND TABLE_NAME = ''' + @table_name + '''
                      FOR XML PATH(''''), TYPE).value(''.'', ''NVARCHAR(MAX)''), 1, 1, '''')'

    EXEC sp_executesql @cmd, N'@columns NVARCHAR(MAX) OUTPUT', @columns = @cols OUTPUT;

    SET @cmd = 'ALTER TABLE ' + @table_name + ' DROP COLUMN ' + @cols;

    EXEC sp_executesql @cmd;

    RETURN 0;

END
GO

✓

EXEC dbo.DropColumns @table_name = 'TB1';
GO

✓

--check results
SELECT TABLE_NAME, COLUMN_NAME
FROM   INFORMATION_SCHEMA.COLUMNS
WHERE  TABLE_NAME = 'TB1';
GO

TABLE_NAME | COLUMN_NAME
:--------- | :----------
TB1        | ABC        
TB1        | ABC1       
TB1        | ABC2       
TB1        | NOTEXIST   
TB1        | NOTEXIST1  
TB1        | NOTEXIST2  

dbfiddle here

---

Related Topics