Types of Regular Expression in SQL Server
- Alphabetic Regex
- Numerical Regex
- Case Sensitive Regex
- Special characters Regex
- Regex to Exclude Characters
The following table describes the wildcards available in the Reference List.
| Expression | Syntax | Description |
|---|---|---|
| Any single character | ? | Matches any single character. |
| Any single digit | # | Matches any single digit. For example, 7# matches numbers that include 7 followed by another number, such as 71, but not 17. |
| Characters not in set | [! ] | Matches any one character that is not specified in the set. |
| One or more characters | * | Matches any one or more characters. For example, new* matches any text that includes "new", such as newfile.txt. |
| Set of characters | [ ] | Matches any one of the characters specified in the set. |
Select *,col from Table.Name where col like '[AL]%'
Starting with first character A and second character L
Select *,col from Table.Name where like '[A][L]%'
Starting character ALL
Select *,col from Table.Name where like '[A][L][L]%'
Select *,col from Table.Name where like 'ALL%'
Starting character between A-D
Select *,col from Table.Name where like '[A-d]%'
First character between A-D and second character between F-I
Select *,col from Table.Name where like '[A-D][F-I]'%
Ending With
Ending Character should be from G-S
Select *,col from Table.Name where col like '%[G-S]'
Staring character A and F and ending character S
Select *,col from Table.Name where col like '[A][F]%[S]'
Excluding Character
Starting character excluding A-T
Select *,col from Table.Name where col like '[^A-T]%'
Specific Pattern
Pattern:
- The first character should be from R and S character – [R-S]
- We can have any combination after the first character – %
- We require the P character – [P]
- It should be followed by either an [P] or [I] – [PI]
- It can have any other character after previous condition- %