Solved

SQL Server Compact Edition Question using PATINDEX

Posted on 2013-06-14
4
374 Views
Last Modified: 2013-07-22
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
0
Comment
Question by:hojohappy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 11

Accepted Solution

by:
Louis01 earned 500 total points
ID: 39247864
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
 
LVL 2

Expert Comment

by:vivekkumarSharma
ID: 39247895
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
 
LVL 11

Expert Comment

by:Louis01
ID: 39247984
:-o ... correction:

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

should be

and LOWER(theColumn) not like '%b%'
...
0
 
LVL 2

Expert Comment

by:vivekkumarSharma
ID: 39270944
thanks for correction Louis01.
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question