Question: How do you return strings containing only letters and digits, with at least one of each? Use three LIKE conditions: reject other characters, require a letter, and require a digit. This filters complete values; it doesn’t remove punctuation from a string.
A reader emailed me with exactly this requirement: keep values containing both letters and numbers and exclude the other values. My first thought was pattern matching. The original answer uses LIKE bracket patterns, not a full regular-expression engine.
Here are the reader’s same five example values and the corrected filter:
IF OBJECT_ID('tempdb..#Alphanumeric184951') IS NOT NULL
THROW 50214, 'Scratch table already exists; inspect it first.', 1;
CREATE TABLE #Alphanumeric184951 (COL varchar(20));
INSERT #Alphanumeric184951 (COL)
VALUES ('90982'), ('SQL SERVER'), ('CA100TEST'), ('0.10002'), ('@#%#$^');
SELECT COL FROM #Alphanumeric184951
WHERE COL COLLATE Latin1_General_100_BIN2 NOT LIKE '%[^0-9A-Za-z]%'
AND COL COLLATE Latin1_General_100_BIN2 LIKE '%[A-Za-z]%'
AND COL COLLATE Latin1_General_100_BIN2 LIKE '%[0-9]%';
DROP TABLE #Alphanumeric184951;
CA100TEST passes all three tests. 90982 has no letters, SQL SERVER contains a space and has no digit, 0.10002 contains a decimal point, and @#%#$^ contains punctuation.
NOT LIKE ‘%[^0-9A-Za-z]%’ means “there is no character outside these permitted ranges.” It does not mean “there are no letters or numbers,” as my original explanation mistakenly suggested. The other two predicates require at least one letter and at least one digit.
The explicit binary collation makes this an ASCII rule for A to Z, a to z, and 0 to 9. LIKE ranges depend on collation; leaving [A-Z] to the database’s default can allow a different set of characters. If your requirement includes accented letters or other writing systems, define that rule separately rather than calling this a universal alphanumeric test.
An empty string or NULL does not pass the combined filter. The temporary table is private to the session and is dropped at the end; its distinct name avoids colliding with the original generic #TEST.
If you know a simpler method for this exact rule, leave it in the comments and I will give you credit. Related: index consolidation, parallel thread row distribution, and trusted constraints.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
5 Comments. Leave new
This looks like better..
select * into #TEST from (
SELECT ‘90982’ AS Data UNION ALL
SELECT ‘SQL SERVER’ UNION ALL
SELECT ‘CA100TEST’ UNION ALL
SELECT ‘0.10002’ UNION ALL
SELECT ‘@#%#$^’) as tbl
select * FROM #TEST
SELECT Data FROM #TEST
WHERE Data NOT LIKE ‘%[^0-9A-Z]%’ AND Data LIKE ‘%[A-Z]%’ AND Data LIKE ‘%[0-9]%’
It is not working for below sample.
CREATE TABLE #TEST (COL VARCHAR(20))
INSERT INTO #TEST
SELECT ‘90982’ AS DATA UNION ALL
SELECT ‘1SQL SERVER’ UNION ALL
SELECT ‘CA100TEST’ UNION ALL– only return this one always.
SELECT ‘0.1A0002’ UNION ALL
SELECT ‘@#%#$4^’
SELECT COL FROM #TEST
WHERE COL NOT LIKE ‘%[^0-9A-Z]%’ AND COL LIKE ‘%[A-Z]%’ AND COL LIKE ‘%[0-9]%’
–Why not simple checking it has string and number together?
SELECT COL FROM #TEST
WHERE COL LIKE ‘%[A-Z]%’ AND COL LIKE ‘%[0-9]%’
The simplified WHERE COL LIKE ‘%[A-Z]%’ AND COL LIKE ‘%[0-9]%’ will return
1SQL SERVER
CA100TEST
0.1A0002
of which only CA100TEST is desired.
An alternative would be to check for the pattern existing/not-existing:
SELECT COL FROM #TEST
WHERE PATINDEX(‘%[A-Z]%’, COL) > 0 — Make sure it has ALPHA
AND PATINDEX(‘%[0-9]%’, COL) > 0 — Make sure it has NUMERIC
AND PATINDEX(‘%[^0-9A-Z]%’, COL) = 0 — Make sure it does not have non-Alphanumeric
Might be faster than two LIKEs and a NOT LIKE.
Is there a way I can do this in Excel??