Please answer to the below question ASP

Question

1.00/5 (1 vote)

See more:

Need SQL query to retrieve records that contains characters other than alpha, - (dash), . (period), or , (comma)

What I have tried:

Select * from PatientDB where FName NOT LIKE '%[A-Z]%' OR NOT FName LIKE '%,%' OR NOT FName LIKE '%.%' OR NOT FName LIKE '%-%'

This query retrieves only those records where FName is(NOT '%[A-Z]%' AND NOT FName LIKE '%,%' AND NOT FName LIKE '%.%' AND NOT FName LIKE '%-%). But I need records where FName contains Alphabets along with numbers and Special characters other than “-“ “,” “.”

Posted 20-Sep-17 1:50am

Member 13419664

Updated 21-Sep-17 0:39am

Add a Solution

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Jochen Arndt · Answer 1 · 2017-09-20T02:07:00

Solution 2

It depends on the SQL server type.

With most, something like this should work:

SQL

SELECT * from PatientDB WHERE FName LIKE '%[^a-zA-Z,.-]%'

If your SQL server supports regular expressions you might use them too (the syntax for the excluded char range is similar).

Posted 20-Sep-17 2:07am

Jochen Arndt

OriginalGriff · Answer 2 · 2017-09-20T02:02:00

Solution 1

Use AND instead of OR.

OR requires any one of the clauses on each side to be true, AND requires both.

Posted 20-Sep-17 2:02am

OriginalGriff

Member 11449311 · Answer 3 · 2017-09-20T06:15:00

You need to familiarize yourself with REGEX and PATINDEX

Use REGEXP with the right regex:
WHERE ...
AND Annotations REGEXP '[-A-Za-z(). ]+'
AND Annotations NOT_REGEXP '[A-Za-z]+'

CREATE TABLE #TableA ( id INT,columnA VARCHAR(200))
INSERT INTO #TableA
SELECT 1,'here is my phone number 12345678. but my cell is 44444444.' UNION ALL
SELECT 2,'987654321 is my balance' UNION ALL
SELECT 3,'i like the number 55555555. it is cool' UNION ALL
SELECT 4,'66666666'

SELECT id, MIN(PATINDEX(n,columnA)) AS pos
FROM #TableA
CROSS APPLY
(
SELECT '%4444%' AS n UNION ALL
SELECT '%123%'
)sub
GROUP BY id
DROP TABLE #TableA

Member 12068587 · Answer 4 · 2017-09-21T00:39:00

Solution 4

You can do like this, and its work on SQL 2014

SELECT * from PatientDB WHERE FName like '%[%]%' or FName like '%.%' or FName like '%,%'

Posted 21-Sep-17 0:39am

Member 12068587