SQL SELECT query for LIKE

Question

I have the following query:

SELECT * FROM student WHERE ssn LIKE '%321321%';

but it returns those records with 113213213215. How can I change it so it only returns records that have 321321 subsequently ONLY.

Answer 1

According to the clarification in the comment you are looking for something like this:

SELECT *
FROM student
WHERE ssn LIKE '%321321%'
AND NOT ssn LIKE '%321321321%'; -- or: AND ssn NOT LIKE '%321321321%';

Keep in mind this will not work if you need to exclude arbitrarily long (>= 4) repetitions of 321. You will need a more complex pattern matching solution if that is a requirement.

User JW 웃 had posted a comment indicating a solution that would exclude all repetitions of 321 greater than 2 (I had initially thought it was wrong, and he deleted his comment, but he was correct):

SELECT ssn
FROM student
WHERE (LEN(ssn) - LEN(REPLACE(ssn, '321', ''))) / LEN('321') = 2;

If possible, I would extract that into a database function. In SQL Server this would look something like:

CREATE FUNCTION SubStringCount
   (@SourceString varchar(max), @SubString varchar(max))
   RETURNS int
AS
BEGIN
   DECLARE @fullLength int = LEN(@SourceString);
   DECLARE @subStringLength int = LEN(@subString);

   DECLARE @nonSubStringChars varchar(max) =
      REPLACE(@SourceString, @SubString, '');

   DECLARE @nonSubStringCharCount int = LEN(@nonSubStringChars);
   DECLARE @subStringCharCount int = @fullLength - @nonSubStringCharCount;
   DECLARE @subStringCount int = @subStringCharCount / @subStringLength;

   RETURN @subStringCount;
END

The query could then be written as:

SELECT ssn
FROM student
WHERE dbo.SubStringCount(ssn, '321') = 2;

Answer 2

SELECT id from student WHERE locate('321321', ssn) > 0 should sort you, according to this. All the best...