asked on

SQL Server Compact Edition Question using PATINDEX

I am using the SQL Server Compact Edition and want to know how I can check for invalid characters in a table column. Since SQL Server Compact Edition does not support character classes I cannot use this expression.

SELECT *
FROM [Addresses.txt]
WHERE PatIndex('%[^a-z0-9]%', [Addresses.txt].[PROD_CD]) > 2

Is there another way to do this using PatIndex or perhaps some other command that is supported by SQL Server Compact Edition? The objective is to identify invalid characters in a table column. For example;

ProductDescription
---------------------------
Black Drill Handle*
White Phone% HeadSet
Blue Computer Case
R^ed IPhone Case& Pen

This query would need list the records that contain an invalid character so based on the data above the following would be listed becuase they contain invalid characters *,%^&

Black Drill Handle*
White Phone% HeadSet
R^ed IPhone Case& Pen

ASKER CERTIFIED SOLUTION

Louis01

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

vivekkumarSharma

Add check constraint to that column.

ALTER TABLE tblName ADD CONSTRAINT CK_ConstraintName
CHECK
(
theColumn not like '%a%'
or theColumn not like '%b%'
or theColumn not like '%c%'
)

Louis01

:-o ... correction:

or LOWER(theColumn) not like '%b%'
...

should be

and LOWER(theColumn) not like '%b%'
...

vivekkumarSharma

thanks for correction Louis01.