LIKE with 2 searches

Question

Post reply

LIKE with 2 searches

wallywizard

Ten Centuries

Points: 1004
More actions
March 11, 2016 at 9:59 am

#308934

I need to find all items that end with B and S.
Not sure what I am doing wrong, if I do just one letter then it works fine. Its when I try to do both in the same query that it messes up. What am I doing wrong?
Here is my code..
SELECT * FROM ITEMMAST
WHERE ITEM LIKE '%S' and '%B'

Viewing 13 posts - 1 through 12 (of 12 total)

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

Alan Burstein SSC Guru Points: 61136 More actions · Answer 1

Try

WHERE Item LIKE '%S' OR Item LIKE '%B'

"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

wallywizard Ten Centuries Points: 1004 More actions · Answer 2

yes that worked! thanks...

1. Why would OR work? OR sounds like I need only either S or B, but in reality I need BOTH. Strange...

2. Is there a way I can do a ORDER BY , to sort the data... ORDER BY the S or B so they are in order?

Alexander Suprun SSCertifiable Points: 6233 More actions · Answer 3

wallywizard (3/11/2016)
yes that worked! thanks...
1. Why would OR work? OR sounds like I need only either S or B, but in reality I need BOTH. Strange...

Could you provide at least one example when ONE SINGLE item ends with 'S' AND 'B' at the same time?

wallywizard (3/11/2016)
2. Is there a way I can do a ORDER BY , to sort the data... ORDER BY the S or B so they are in order?

Is this what you need?

ORDER BY RIGHT(item, 1)

Alex Suprun

wallywizard Ten Centuries Points: 1004 More actions · Answer 4

sorry let me rephrase....I dont have an item that ends both S and B at the same time.

There are tons of items, I am only looking to pull items that end with B (4857B) and items that end with S (1240S)

That OR function worked great, but what I was saying is that OR sounds like it wants to pull only one of these.. I assumed AND would work because I want both B AND S.

I tried your order by but doesnt seem to sort my data. (pic attached)

Alexander Suprun SSCertifiable Points: 6233 More actions · Answer 5

wallywizard (3/11/2016)
I tried your order by but doesnt seem to sort my data. (pic attached)

Try harder.

Alex Suprun

cphite SSCarpal Tunnel Points: 4272 More actions · Answer 6

wallywizard (3/11/2016)
sorry let me rephrase....I dont have an item that ends both S and B at the same time.
There are tons of items, I am only looking to pull items that end with B (4857B) and items that end with S (1240S)
That OR function worked great, but what I was saying is that OR sounds like it wants to pull only one of these.. I assumed AND would work because I want both B AND S.
I tried your order by but doesnt seem to sort my data. (pic attached)

OR does only want to pull one of these - which is what you want. Each individual row is considered according to the entire WHERE clause - that is, for a row to appear in the results, everything in the WHERE clause must be true for that row.

When you use OR what you're telling SQL is that each row must have an item that ends in "B" or that ends in "S" - you don't care which one, it has to be one of those two.

When you use AND what you're telling SQL is that each row must have an item that ends in "B" AND that ends in "S" - which is logically impossible, so you'll never get results.

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

You may already know this, but having that leading wildcard in the search will prevent SQL Server from using an index on that column, so your query is going to run slowly. One alternative to this is to create a computed persisted column to store the right 1 character, then put a nonclustered index on that column. Then you won't have to search against the item number, but rather by the column that holds the single character. The column would also work for your sort.

You could then continue to use the OR, or you can use an IN clause like this:

SELECT *

FROM ITEMMAST

WHERE LastChar IN ('B', 'S')

ORDER BY LastChar;

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

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 8

Ed Wagner (3/11/2016)
You may already know this, but having that leading wildcard in the search will prevent SQL Server from using an index on that column, so your query is going to run slowly. One alternative to this is to create a computed persisted column to store the right 1 character, then put a nonclustered index on that column. Then you won't have to search against the item number, but rather by the column that holds the single character. The column would also work for your sort.
You could then continue to use the OR, or you can use an IN clause like this:
SELECT *
FROM ITEMMAST
WHERE LastChar IN ('B', 'S')
ORDER BY LastChar;

+1

You've gotta know what's coming next... someone will want the value prior to the last character. An additional computed, persisted, indexed column for that should probably be a "just do it so when they ask, it's already done" moment.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

planetmatt SSCrazy Points: 2201 More actions · Answer 9

Alan.B (3/11/2016)
Try
WHERE Item LIKE '%S' OR Item LIKE '%B'

WHERE Item like '%[S,B]'

Ed Wagner SSC Guru Points: 287024 More actions · Answer 10

planetmatt (3/14/2016)
Alan.B (3/11/2016)
Try
WHERE Item LIKE '%S' OR Item LIKE '%B'
WHERE Item like '%[S,B]'

That leading wildcard in the search will force a scan of the column, so the query will run slowly. An index, if present, won't be used. The persisted computed column is a better approach.

Edit: For regular expressions, I think the pipe (|) is the character for an OR. I don't use them much because they're also slow.

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

Luis Cazares SSC Guru Points: 183698 More actions · Answer 11

Ed Wagner (3/14/2016)
planetmatt (3/14/2016)
Alan.B (3/11/2016)
Try
WHERE Item LIKE '%S' OR Item LIKE '%B'
WHERE Item like '%[S,B]'
That leading wildcard in the search will force a scan of the column, so the query will run slowly. An index, if present, won't be used. The persisted computed column is a better approach.
Edit: For regular expressions, I think the pipe (|) is the character for an OR. I don't use them much because they're also slow.

It should actually be:

WHERE Item LIKE '%[SB]'

On the other side, I agree with the use of a computed column. I just disagree on which should it be. The Item column should be splitted into 2 different columns if each part represents a different attribute, and then it should be joined for display.

For the ORDER, the column might be a char with trailing spaces. RIGHT won't eliminate trailing spaces on char columns, only on varchar.

CREATE TABLE #Test( Item char(10), Item2 varchar(10));

INSERT INTO #Test VALUES('12345B','12345B');

SELECT RIGHT(Item,1), RIGHT(RTRIM(Item),1),

RIGHT(Item2,1), RIGHT(RTRIM(Item2),1)

FROM #Test;

GO

DROP TABLE #Test;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

wallywizard Ten Centuries Points: 1004 More actions · Answer 12

cphite (3/11/2016)
wallywizard (3/11/2016)
sorry let me rephrase....I dont have an item that ends both S and B at the same time.
There are tons of items, I am only looking to pull items that end with B (4857B) and items that end with S (1240S)
That OR function worked great, but what I was saying is that OR sounds like it wants to pull only one of these.. I assumed AND would work because I want both B AND S.
I tried your order by but doesnt seem to sort my data. (pic attached)
OR does only want to pull one of these - which is what you want. Each individual row is considered according to the entire WHERE clause - that is, for a row to appear in the results, everything in the WHERE clause must be true for that row.
When you use OR what you're telling SQL is that each row must have an item that ends in "B" or that ends in "S" - you don't care which one, it has to be one of those two.
When you use AND what you're telling SQL is that each row must have an item that ends in "B" AND that ends in "S" - which is logically impossible, so you'll never get results.

Great tip, thanks for that, helps break it down better. And thanks to everyone, Im a newbie with sql and your posts help alot. Building my knowledge one post at a time..... and knowing is half the battle...GI Jooeeeeeeeeeeee (not sure if anyone will get that last part 😉 )