SQL Server Pattern - Retrieving records from F1AGE to F20AGE

Data and Databases
1

I’m attempting to extract records that match a specific pattern in SQL. My query looks like this:

SELECT * 
FROM TEST 
WHERE QUESTION LIKE '%F[1-9]AGE%'

This correctly identifies entries from F1AGE to F9AGE. However, I am now looking for a way to include F1AGE through F20AGE. Does anyone have a solution for this pattern matching?

2

To extend your pattern matching from F1AGE to F20AGE, you can break it into two parts within your LIKE clause. Firstly, you’ll want to include LIKE 'F[1-9]AGE%' for F1AGE to F9AGE. Secondly, you will add another condition for double-digit ages: LIKE 'F1[0-9]AGE%' for F10AGE to F19AGE and LIKE 'F20AGE%' specifically for F20AGE. Combine them using OR to include the full range:

SELECT *
FROM TEST
WHERE QUESTION LIKE 'F[1-9]AGE%'
   OR QUESTION LIKE 'F1[0-9]AGE%'
   OR QUESTION LIKE 'F20AGE%'
3

Another way u might try is using a regex pattern (if ur SQL supports it). With regex, you can create a match from F1AGE to F20AGE more flexibly. Some SQL systems have regex functions like REGEXP. just be sure to check if it’s available!

4

hey Silvia85! Have you thought about using a CASE statement? it might sound unconventional, but it can be creative when matching patterns that dont fit into simple LIKE clauses. Could be something that challenges ur sql skills and brings a fresh approach to ur query!

5

If ur SQL server supports string manipulation functions, try turning QUESTION into a substring and compare numerics directly. Use pattern matching funtions to grab numbers between F and AGE, then apply a num range with WHERE clause. Makes it a lil cleaner too if u have complex queries later!

6

Have you considered using a series of nested conditions to achieve this? By splitting your logic across multiple checks, you can construct a case to match from F1AGE to F20AGE. You can extend your WHERE clause with additional conditions using BETWEEN. Ensure you write down an expression that evaluates the numeric parts separately, check for numbers between 1 and 20 after stripping out the “F” and “AGE” parts, if your SQL environment supports such operations.