2

Im facing this problem: I need to select columns with only one occurence of dash in table like this:

Id   Name
1    Boots - black
2    Sneakers - Logan - white
3    Shirt - white
4    Pants - cargo - blue
5    Hat – green

So it will type out only column 1,3 and 5 - because they have only one dash and others have 2 or more.
Ive tried this query: Select * from products where Name like “%-%“; ,but it selected all occurences of dash, no matter if theres one or more.
Thanks a lot!

RiggsFolly
  • 93,638
  • 21
  • 103
  • 149
Rory
  • 23
  • 2

1 Answers1

2

You can use LENGTH and REPLACE:

SELECT *
FROM products 
WHERE (LENGTH(Name) - LENGTH(REPLACE(Name, '-', ''))) = 1

If there is only one "dash" in the Name, the calculation of LENGTH(Name) - LENGTH(REPLACE(Name, '-', '')) will return one.

Arulkumar
  • 12,966
  • 14
  • 47
  • 68