How to find if there is only one hyphen in a column SQL Server 2008?

Question

Suppose I have a table 'customer' like this:

+--------------------------------------------------+--------+
|                     Customer                     |   ID   |
+--------------------------------------------------+--------+
| OSHKOSH CORPORATION-17180                        | cust12 |
| L&T-IES-P&G KABUSHIKI KAISHA-4216                | cust13 |
| THE PROCTER-GAMBLE MANUFACTURING COMPANY-17214   | cust14 |
+--------------------------------------------------+--------+

I should get only one row since column customer has only one hyphen.

| OSHKOSH CORPORATION-17180                        | cust12 |

There's no direct function for this, but you can do it with a replace and len. Similar kind of question is here : http://stackoverflow.com/questions/287373/how-can-you-find-the-number-of-occurrences-of-a-particular-character-in-a-string — Shiju Shaji, Sep 18 '15 at 04:21

score 3 · Accepted Answer · answered Sep 18 '15 at 03:59

3

Without considering the performance, below sql can achieve this.

select *
from customer
where (len(Customer) - len(replace(Customer, '-', ''))) = 1

answered Sep 18 '15 at 03:59

daniel

1,010
6
15

score 3 · Answer 2 · answered Sep 18 '15 at 06:33

3

select *
from customer
where customer like '%-%'
and customer not like '%-%-%'

The first check is for at least one -, the second is for not at least two. Combined it means exactly one.

answered Sep 18 '15 at 06:33

How to find if there is only one hyphen in a column SQL Server 2008?

2 Answers2