CODEWITHSUNDEEP
sqlt-sql
p.campbell
p.campbell

Reputation: 100557

TSQL: string operations to match a pattern or wildcard for one character

Consider the need to query for a certain pattern of data within a column. The example I'll use are customers with Canadian postal codes.

ID       Postal
--       -------
442      90210  
631      T0R 4C2
447      YO31 1EB
145      F9S8S6
73       K9J 3K3

Pretend you don't have an easy out (like a state/prov or country field), or that you're running a non-conformance report. Yes, don't trust user input!

-- we want to find: three chars + space + 3 chars 'XXX XXX'
-- LIKE % is not terribly helpful
SELECT * FROM SomeTable 
WHERE  Postal LIKE  --?

We want the resultset to be

ID       Postal
--       -------
631      T0R 4C2
73       K9J 3K3

Question: how would you formulate that LIKE clause?

Upvotes: 0

Views: 1046

Answers (2)

Justin Grant
Justin Grant

Reputation: 46663

Underscore matches one character only. Is this what you're looking for?

LIKE '___ ___'

Upvotes: 3

Lukasz Lysik
Lukasz Lysik

Reputation: 10610

SELECT * 
FORM SomeTable 
WHERE Postal LIKE '___ ___'

Or even better, when you want to specify exact numbers-letters, you can do this:

SELECT * 
FORM SomeTable 
WHERE Postal LIKE '[a-z][a-z][a-z] [0-9][0-9][0-9]'

It depends of the type of code you want to get.

Upvotes: 2

Related Questions