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