4

how do i write a query to retrieve a pattern of exactly five letters. I have this question to answer : List all the Customer name whose names are formed by exactly 5 letters. The query i wrote is like this :

SELECT ContactName
From Customers
 WHERE ContactName LIKE '[A-Z][A-Z][A-Z][A-Z][A-Z]'

Thank you

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Abdulrahman Samhoury
  • 49
  • 1
  • 6
    That seems reasonable. If you have a case sensitive collation, you might want to include lower case too. – Gordon Linoff Feb 26 '18 at 18:56
  • 4
    There are multiple ways. The way you have done is one. You could also use `LEN()` and the `LIKE` operator. Something like... `ContactName not like '%[^a-z]%' and Len(ContactName) = 5` – S3S Feb 26 '18 at 18:56
  • To address case sensitive collation, in my above comment just add `Lower(ContactName)` – S3S Feb 26 '18 at 19:21

3 Answers3

0
SELECT ContactName
FROM Customers
WHERE LEN(ContactName) = 5 AND ContactName Not LIKE '%[0-9]%' AND ContactName Not LIKE '%[^a-zA-Z0-9]%'

I haven't tested it but it should work

My Approach is Check the Length which should be equal to 5 and should not contain any number or special character.

I took help form the How to detect if a string contains at least a number?

And also reference from How to detect if a string contains special characters? And read

https://www.educative.io/edpresso/what-is-not-like-operator-in-sql

Noob Coder
  • 444
  • 1
  • 5
  • 16
  • ContactName should be 5 *letters*, not any character, but letters – Martin Jan 31 '20 at 10:43
  • Another And with Not LIKE '%[^a-zA-Z0-9]%' – Noob Coder Jan 31 '20 at 10:45
  • 1
    @Martin I have updated the answer and added reference to where the code was taken from so it will be helpful to other users – Noob Coder Jan 31 '20 at 10:47
  • Your final two arguments are counter productive ; you're setting if column is NOT number and then you are checking if the column is a Number or a letter.... make your mind up! – Martin Jan 31 '20 at 14:56
  • @Martin look closely its %[^a-zA-Z0-9]% with a ^ – Noob Coder Feb 04 '20 at 13:53
  • well that's a double negative; why not remove both the negatives and simply the result? *NOT like NOT this pattern* – Martin Feb 04 '20 at 19:36
  • And if you're doing the same thing twice, you can simply remove the first ` Not LIKE '%[0-9]%'` clause entirely as that is caught in the second clause! – Martin Feb 04 '20 at 19:36
  • first clause it the check for numbers and the second clause is the check for special characters – Noob Coder Feb 05 '20 at 04:08
-1
SELECT ContactName
FROM Customers
WHERE LEN(ContactName) = 5
QuestionAsker
  • 117
  • 9
-3
SELECT ContactName
From Customers
WHERE ContactName LIKE '_%_%_%_%_%'

Any character with length 5

S3S
  • 24,809
  • 5
  • 26
  • 45
hugmax
  • 32
  • 6