SQLTeam.com | Weblogs | Forums

SQL Pattern Matching

Hey, I have been trying to extract specific patterns from a sample table but result is always empty.

Another example is as follows I am trying to postal codes with similar pattern from a data set but the result is null when i execute the query

Could there be a trailing space or other char in the column? Try adding a percent to the end your LIKE string:

LIKE '...%'

1 Like

You may also need to include upper case in your patterns:

LIKE '[a-zA-Z][a-zA-Z][0-9]' ... etc.

Especially if the column has a case-sensitive collation on it.

2 Likes

working for me ... i am using

Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64) Sep 24 2019 13:48:23 Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit) on Windows 10 Pro 10.0 (Build 19044: )

this is my collation
SQL_Latin1_General_CP1_CI_AS

It may be that there are non-printable characters in the postal code. You can test this using the following

SELECT 
    CAST(PostalCode AS VARBINARY(MAX)), CAST('WA1 1DP' AS VARBINARY(MAX))
FROM
   YourTable
WHERE
   CustomerId=4

If the two columns in the result set are not the same, that is an indication that there are unprintable characters in your data, or perhaps there trailing or leading spaces, or more than one space where you expected only one space etc.

2 Likes

Thankyou everyone. Postalcode contained case sensitive characters.

1 Like