Click here to Skip to main content
15,906,816 members
Please Sign up or sign in to vote.
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
Updated 21-Sep-17 0:39am

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).
 
Share this answer
 
Use AND instead of OR.

OR requires any one of the clauses on each side to be true, AND requires both.
 
Share this answer
 
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
 
Share this answer
 
You can do like this, and its work on SQL 2014

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

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900