Student codes often follow a rule such as STU-1001: three letters, a hyphen, and four digits. SQL Server 2025 adds REGEXP_LIKE for checking more detailed text patterns. This lesson shows how to find codes that match a simple rule and how to read the pattern one piece at a time.
^ start → STU- prefix → [0-9]{4} four digits → $ end
STU-1001 matches · STU-10A1 does not
Before you run the query
Use SQL Server 2025 (17.x) and a database at compatibility level 170. Check your current database with the first query below. Ask your database administrator before changing a shared database's compatibility level, because that setting affects more than this lesson. SQL Server 2022 and earlier do not provide these regular expression functions.
SELECT name, compatibility_level
FROM sys.databases
WHERE name = DB_NAME();
Create and search sample students
The temporary table exists only for your session. Run this complete script in a practice database:
CREATE TABLE #Students
(
StudentName nvarchar(50) NOT NULL,
StudentCode varchar(20) NOT NULL
);
INSERT INTO #Students (StudentName, StudentCode)
VALUES ('Asha', 'STU-1001'),
('Ravi', 'STU-1002'),
('Mina', 'STU-10A3'),
('Leena', 'OLD-1004');
SELECT StudentName, StudentCode
FROM #Students
WHERE REGEXP_LIKE(StudentCode, '^STU-[0-9]{4}$');
DROP TABLE #Students;
The result contains Asha and Ravi. Mina has a letter where the pattern requires a digit. Leena has a different prefix. The ^ and $ anchors require the whole code to match, so extra text before or after it is rejected.
How is this different from LIKE?
LIKE 'STU-%' can find a prefix, but it does not say that exactly four digits must follow. REGEXP_LIKE describes that structure with [0-9]{4}. Use the simpler LIKE when a basic wildcard is enough. A regular expression checks text shape; it does not prove that a student exists or that a code is unique.
Change the matching rule
Use '^STU-[0-9]{4}$', 'i' as the second and third arguments to allow a lowercase stu- prefix. The i flag means case-insensitive matching. Without it, the default is case-sensitive. Regex matching does not follow every SQL collation rule, so test patterns against your real data.
Practice
- Add
STU-9999andSTU-10001. Predict which one matches. - Change the pattern to accept three or four digits:
^STU-[0-9]{3,4}$. - Explain why the anchors matter when validating a complete code.
Continue with the SQL learning path. Read Microsoft's REGEXP_LIKE reference and regular expression function guide for platform and syntax details.
No comments:
Post a Comment
Please do not enter any spam link in the comment box.