CODEWITHSUNDEEP
sql
Robert Winchester
Robert Winchester

Reputation: 23

SQL Like ending in specific string %5% vs. %25%

Hi and thanks in advance. I have a few case statements that calculate "Quantity" based on the text in "ProductName."

Productname                Quantity
Product1 - 5 pack          should = 5
Product2 - 10 pack         should = 10
Product3 - 25 pack         should = 25

My case works well, however, when I do this: When productname like '%5 pack' then quantity(5) it also sees this as "25 pack" because of the wildcard and thus I get incorrect values. They all end exactly as seen here "- [##] pack"

Upvotes: 0

Views: 390

Answers (3)

Brennan Pope
Brennan Pope

Reputation: 1073

Depending on your data, you could use LIKE '%[ -]5 pack'. (Notice that inside the brackets is a space and a hyphen.)

This keeps you from having to order the case statement correctly.

Upvotes: 0

shawnt00
shawnt00

Reputation: 17915

Case expression are searched in order. Put the more restrictive ones first.

Or if all the names fit the pattern you describe then this will probably work.

select cast(left(right(ProductName, 7), 2) as int) as Quantity from ...

Or you could use substring with a negative start position potentially.

cast(substring(ProductName, -7, 2) as int)

Many other options as well...

Upvotes: 1

Robert Winchester
Robert Winchester

Reputation: 23

I'm not sure if there's a better answer, but I started thinking in order of operation and it turns out that if I use this first in the statement When productname like '%25 pack' then use When productname like '%5 pack' everything seems to be working.

I'd be interested in knowing if there is an easier or better way of doing this.

Upvotes: 0

Related Questions