A reader was looking the other day to find how they might find a percent character (percent sign, or %) within a string using the LIKE operator. Check the code below.
USE tempdb
CREATE TABLE Terms
(TermID int IDENTITY NOT NULL PRIMARY KEY
,CreditTerm varchar (25)
);
GO
INSERT INTO Terms (CreditTerm) VALUES ('2/10, n/30');
INSERT INTO Terms (CreditTerm) VALUES ('3/10, n/30');
INSERT INTO Terms (CreditTerm) VALUES ('2%');
INSERT INTO Terms (CreditTerm) VALUES ('20%');
INSERT INTO Terms (CreditTerm) VALUES ('200%');
GO
--1. Plain Jane LIKE statement, level –100
SELECT *
FROM Terms
WHERE CreditTerm LIKE '20%'
--2. Find a '%' using LIKE in the string.
SELECT *
FROM Terms
WHERE CreditTerm LIKE '2[%]'
--3. Find all 2% discounts
SELECT *
FROM Terms
WHERE CreditTerm LIKE '2[%-/]%'
--How do we know which is first (in order)?
--Use ASCII to see which is the lesser value
--We know to put % before /
SELECT ASCII('%'), ASCII('/')
----------- -----------
37 47
(1 row(s) affected)
The first is an elementary SQL statement using LIKE. No problems with this one I hope. Next, the one that the reader was looking for; here we use square brackets to signal that the percent sign is a literal, so it pops out for us. Good stuff. Number three is slightly more interesting because if someone wants to get all of the 2% discounts (and not 20% or 200%), which includes 2%, and the accounting credit term 2/10, n30 (or 2% discount in 10 days, payable by 30 days from invoice date), we use this one. Brackets with values separated by a dash tell the LIKE to search from the first to the second. One issue on this one – you have to know which to put first. It’s easy if it’s letters ([a-d]), but in order to find the values of ours, use ASCII keyword to return the character set value. Obviously 37 comes before 47, so we have the order correct. If we tried to put the forward slash (/) first, we’d get nothing in our result set.
Lee
----------------------------
Like ask me if I care ok?
