?
Solved

Sql Statement: Text Does not contain numbers or symbols

Posted on 2005-03-23
9
Medium Priority
?
538 Views
Last Modified: 2012-06-21
Hello everyone.

I am really rusty with SQL statements so I was wondering, what SELECT statement would be ideal for filtering out a Text field that contains numbers or symbols ie []{}(), etc....

SELECT * FROM table WHERE txtField.......

Thanks.
0
Comment
Question by:carpetflyer
[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
9 Comments
 
LVL 51

Assisted Solution

by:Gustav Brock
Gustav Brock earned 80 total points
ID: 13616219
Could be:

SELECT * FROM table WHERE Instr(txtField, "[") > 0 OR Instr(txtField, "(") > 0 OR ...

/gustav
0
 
LVL 5

Expert Comment

by:MitchellVII
ID: 13616805
How about using Eval()?

M
0
 
LVL 41

Accepted Solution

by:
shanesuebsahakarn earned 240 total points
ID: 13617100
Or alternatively:
SELECT * FROM MyTable WHERE MyField Like "*[{}*()0123456789]*" Or MyField Like "*]*"

The first Like should be:
Like "*[<all characters you want to filter for, except for the ] character>]*"

The second like traps the ] character.
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 3

Author Comment

by:carpetflyer
ID: 13617673
Thanks for the responses everyone.

So shanesuebsahakarn, Can you put reg expressions in sql statements? What you gave me looks like it. So i can do: MyField Like "[a-zA-Z]" meaning MyField will grab records that contain one letter ie a or b or c, etc etc??

Thanks.
0
 
LVL 2

Expert Comment

by:mingfattt
ID: 13617729
SELECT FieldName
FROM TableName
WHERE (((FieldName) Like "[****]"));

This will grab all the letter inside....

0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 13617744
Carpetflyer yep, you can certainly use your example, but Like doesn't support very powerful regular expressions. Note - you don't need both a-z and A-Z - Like is not case sensitive, so a-z will match both A and a.
0
 
LVL 3

Author Comment

by:carpetflyer
ID: 13617765
>>but Like doesn't support very powerful regular expressions

So what does support reg exp in SQL? Just curious thats all.

Thanks again.
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 13617778
You can use the VB script regular expression library (by adding the library into references):
http://authors.aspalliance.com/brettb/VBScriptRegularExpressions.asp

but you'll have to write some code to perform the comparison.
0
 
LVL 3

Author Comment

by:carpetflyer
ID: 13617826
Wow, interesting...i'll keep this in mind.

Thanks for the link.

Thank you again everyone! :)
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

800 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