LIKE Operator OR Regular Expressions?

Question

Post reply

LIKE Operator OR Regular Expressions?

SQLWannabe

SSCrazy

Points: 2842
More actions
February 25, 2013 at 11:36 am

#268980

If I have a table with a field in it called RECIPE_NAME and I want to see which recipes have any two capital letters next to each other, my thought was to write a query like this:
select * from table where INGREDIENT_NAME LIKE ('%[A-Z][A-Z]%').
However I get almost every record in the results. The following are values in the field:
1. fresh chives (shouldn't be in the result set)
2. MIRACLE WHIP Light dressing (should be in the result set)
I thought this would be quite easy. Can anyone give me any help?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Does this help?

DECLARE @TestTable TABLE (

TestTableID int IDENTITY(1,1),

TestValue varchar(30)

);

INSERT INTO @TestTable(TestValue)

VALUES ('fresh chives'),('MIRACLE WHIP');

SELECT * FROM @TestTable;

SELECT * FROM @TestTable WHERE TestValue LIKE '%[A-Z][A-Z]%';

SELECT * FROM @TestTable WHERE TestValue COLLATE Latin1_General_BIN2 LIKE '%[A-Z][A-Z]%';

SQLWannabe SSCrazy Points: 2842 More actions · Answer 2

Lynn,

Holy cr@p, that's exactly what I want.

I didn't even think about the collation.

So since my collation is: SQL_Latin1_General_CP1_CI_AS, I need to change the collation?

Can you give me a short explanation on why?

And I really appreciate your quick (and on-target) answer.

Alan Burstein SSC Guru Points: 61141 More actions · Answer 3

SQLWannabe (2/25/2013)
Lynn,
Holy cr@p, that's exactly what I want.
I didn't even think about the collation.
So since my collation is: SQL_Latin1_General_CP1_CI_AS, I need to change the collation?
Can you give me a short explanation on why?
And I really appreciate your quick (and on-target) answer.

You don't need to change the collation on your db (if that is what you are asking).

SQL_Latin1_General_CP1_CI_AS IS NOT case sensitive (that what the CI means) ; the collation Lynn used IS case sensitive. If you need to distinguish between upper and lower case then all you need to do is to use the Collate statement and select a case sensitive collation like Lynn did.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Ed Wagner SSC Guru Points: 287026 More actions · Answer 4

One other noteworthy point is that is you are set up for case insensitivity, the SQL approach won't work. The regular expression approach works either way.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

SQLWannabe SSCrazy Points: 2842 More actions · Answer 5

Alan,

Thanks for the resposne. I should have been more clear. What I should've said was:

"I need to change the collation in my query".

The COLLATE function/keyword is definitely some food for thought. It's not something that normally comes to mind. I didn't know you could do what Lynn did with the COLLATE function/keyword.

I gotta admit it, this forum is AWESOME!!!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Ed Wagner (2/25/2013)
One other noteworthy point is that is you are set up for case insensitivity, the SQL approach won't work. The regular expression approach works either way.

Really, isn't that what I did with my code, used SQL to solve the problem?

Ed Wagner SSC Guru Points: 287026 More actions · Answer 7

My mistake. I was referring to the regular expressions approach you took in SQL working either way as opposed to some string parsing approach.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Alan Burstein SSC Guru Points: 61141 More actions · Answer 8

SQLWannabe (2/25/2013)
Alan,
Thanks for the resposne. I should have been more clear. What I should've said was:
"I need to change the collation in my query".
The COLLATE function/keyword is definitely some food for thought. It's not something that normally comes to mind. I didn't know you could do what Lynn did with the COLLATE function/keyword.
I gotta admit it, this forum is AWESOME!!!

I thought you were asking how to make the change in your query...

Because today is a little slow for me I thought I'd show you how you an alternative method. I would never actually do this because COLLATE does the trick. This is more of a demo of the power of the Tally table[/url]:

-- Sample data

DECLARE @testData TABLE (txt varchar(10));

INSERT INTO @testData VALUES ('AXAxxx'), ('BxByyy'), ('xyz');

--Case Insensitve

SELECT COUNT(*) AS [Case Insensitve] FROM @testData

WHERE txt LIKE '%[A-Z][A-Z]%';

--Case Sensitve

SELECT COUNT(*) AS [Case Sensitve] FROM @testData

WHERE txt COLLATE Latin1_General_BIN2 LIKE '%[A-Z][A-Z]%';

-- a set-based way to do this without COLLATION

;WITH

testData AS (SELECT * FROM @testData),

nums(n) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM [master].dbo.spt_values Tally),

matrix(w, n,s,c1,c2) AS (

SELECT td.txt, n, SUBSTRING (td.txt,n,1), ASCII(SUBSTRING(td.txt,n,1)), ASCII(SUBSTRING(td.txt,n+1,1))

FROM nums n

OUTER APPLY @testData td

WHERE n<=LEN(td.txt))

SELECT COUNT(DISTINCT w) AS [Case Sensitve]

FROM matrix

WHERE (c1>=65 AND c1<=90) AND (c2>=65 AND c2<=90)

Edit: Typo, Minor code change.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001