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
hojohappyAsked:
Who is Participating?
 
Louis01Connect With a Mentor Commented:
Only alternative I can think of (in SQL) is to find strings with non-illegal characters.

select *
from theTable
where LOWER(theColumn) not like '%a%'
or LOWER(theColumn) not like '%b%'
or LOWER(theColumn) not like '%c%'
...

Not very effective...

Perhaps a javascript would work for you?

<script language="JavaScript">
var temp = new String('¿This is a testing... of St???¿ra What.@#..');  
document.write(temp + '<br>');  
temp = temp.replace(/[^a-zA-Z 0-9]+/g,'');  
document.write(temp + '<br>');  
</script>  

Open in new window

0
 
vivekkumarSharmaCommented:
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%'
)
0
 
Louis01Commented:
:-o ... correction:

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

should be

and LOWER(theColumn) not like '%b%'
...
0
 
vivekkumarSharmaCommented:
thanks for correction Louis01.
0
All Courses

From novice to tech pro — start learning today.