SQL Server – WHERE Clause Pattern Matching for Rows with Characters Not in List

sql serversql-server-2008

I am in a SQL Server 2008 environment.

I am trying to use pattern matching in the WHERE clause to find rows where a certain column's value contains characters that are not alpha-numeric, underscore, dash, period, or space.

This is my code, and sample data, but I am not getting the results expected.

In the example data, I want to return rows 7, 8, 9, and 12, but I am getting rows 5 and 6.

If this isn't the best way to achieve the goal, I am open to hearing other methods.

I am not in an environment where I can implement regex so my solution limited to out-of-the-box functionality.

CREATE TABLE PATTERN_TEST
(
ID INT NOT NULL,
STRING NVARCHAR(40) NOT NULL
)

INSERT INTO PATTERN_TEST
SELECT 1, 'string' UNION 
SELECT 2, 'STRING' UNION 
SELECT 3, 'string space' UNION 
SELECT 4, 'STRING SPACE' UNION 
SELECT 5, 'string-dash' UNION 
SELECT 6, 'string-dash space' UNION 
SELECT 7, 'string "otherchar"' UNION 
SELECT 8, 'string "other char"' UNION 
SELECT 9, '"string"' UNION 
SELECT 10, 'string_underscore' UNION 
SELECT 11, 'string_underscore space' UNION
SELECT 12, '"'
;

SELECT * FROM PATTERN_TEST WHERE STRING LIKE '%[^a-zA-Z0-9_ -.]%';

Best Answer

This will work as well:

SELECT * 
FROM PATTERN_TEST 
WHERE STRING LIKE '%[^a-zA-Z0-9_ .-]%' ;

Tested at rextester.com

The only difference is that dash (-) is put at the end of the [...] pattern. It has to do with the special meaning of the dash character (A-Z is interpreted as "any character from A to Z").

In your pattern the last three characters (space-dash-dot: -.) are interpreted as "any character from space () to dot (.)", and the results are not the wanted/expected.

You could also escape the dash:

WHERE STRING LIKE '%[^a-zA-Z0-9_ $-.]%' ESCAPE '$' ;

Note though that using ESCAPE may adversely affect cardinality estimation.

See the MSDN documentation for some details: LIKE:

If there is no character after an escape character in the LIKE pattern, the pattern is not valid and the LIKE returns FALSE. If the character after an escape character is not a wildcard character, the escape character is discarded and the character following the escape is treated as a regular character in the pattern. This includes the percent sign (%), underscore (_), and left bracket ([) wildcard characters when they are enclosed in double brackets ([ ]). Also, within the double bracket characters ([ ]), escape characters can be used and the caret (^), hyphen (-), and right bracket (]) can be escaped.

To be clear: special characters need escaping when they are used as non-special. Inside [], the three (^, -, ]) are special. Putting the dash at the end is more like a hack.

Related Solutions

SQL Server Unicode Pattern Matching – How to Solve Issues

See if doing a binary collate fits what you need. Here is a quick test.

USE Tempdb  
GO

IF OBJECT_ID('PattMatch') IS NOT NULL  
BEGIN  
  DROP TABLE PattMatch  
END  
GO  

CREATE TABLE PattMatch (COL1 NVARCHAR(50))  
GO  

INSERT INTO PattMatch  
VALUES (nchar(46797)),(nchar(14843)),('ddddddd*'),('lettersand9999')  
GO  

DECLARE @Pattern nvarchar(50) = N'%[^a-z]%'   
SELECT PatIndex(@Pattern, COL1 COLLATE Latin1_General_BIN2), COL1 FROM PattMatch  
GO  

DROP TABLE PattMatch   
GO  

--your test  
DECLARE @Pattern nvarchar(50) = N'%[^a-z]%'  
SELECT PatIndex(@Pattern, nchar(46797) COLLATE Latin1_General_BIN2)  
SELECT PatIndex(@Pattern, nchar(14843) COLLATE Latin1_General_BIN2)

Sql-server – deteriorating stored procedure running times

What is up with FROM part JOIN model ON 1=1? This the same as FROM part, model, which is a cartesian join and will result in a very large number of rows. Is that join supposed to be like that?

You will likely help us help you if you provide details about the tables involved. Please "script" the definition of the tables, along with any indexes defined on those tables.

This sounds like a classic case of parameter sniffing resulting in good plan/bad plan choices for various scenarios in your data.

You may be able to get more reliable performance by making SQL Server cache different plans for different scenarios by using sp_executesql, as in the following example:

CREATE PROCEDURE [dbo].[create_grid_materials2] 
(
    @partlistid bigint
    , @pid bigint
    , @masterid bigint
)
AS
BEGIN
    begin
        DECLARE @cmd NVARCHAR(MAX);

        SET @cmd = '   
        INSERT INTO material (partid, personid, modelID)
        SELECT 
            partid = part.id
            , personid = @pid
            , modelid = model.id  
        FROM part
            INNER JOIN model ON 1=1
        WHERE (
            model.masterid = ' + CONVERT(NVARCHAR(50), @masterid) + ' 
                AND model.modelSetID IS NULL
                AND part.partlistid = ' + CONVERT(NVARCHAR(50), @partlistid) + '
                AND (
                    part.partType = 100 
                    or part.partType=120 
                    or part.partType = 130
                )
            )
            AND NOT EXISTS (
                SELECT 1 
                FROM material AS a1 
                WHERE a1.partid = part.id 
                    AND a1.personid=@pid 
                    AND a1.modelid=model.id
                )';
        DECLARE @Params VARCHAR(200);
        SET @Params = '@pid INT';
        EXEC sys.sp_executesql @cmd
            , @Params
            , @pid = @pid;
    end
End

The above code will cause a new plan to be generated for each combination of @partlistid, and @masterid.

The presumption here is some combinations of those two variables lead to a very small number of rows, whereas some combinations lead to a very large number of rows.

Forcing a plan for each combination allows SQL Server to generate more efficient plans for each. I've explicitly not included @pid since you probably want to try it with a fairly small number of combinations first; adding a third variable to the mix will make for an exponentially larger number of possible plans.

Best Answer

Related Solutions

SQL Server Unicode Pattern Matching – How to Solve Issues

Sql-server – deteriorating stored procedure running times

Related Question