Find multiple hyphen in a string using case statement

Transact-SQL
,
1

I had a string with single hyphen , multiple hyphen. if a string doesn't have the hyphen I will retain the string. if found a single hyphen I will retain the string before the hyphen. if found multiple hyphen I will retain the whole string. how can I achieve this scenario using SQL 2008 R2. thank you in advance.

below sample DDL and result

[code]DECLARE @Sample TABLE
(
item nvarchar(20)
)

INSERT @Sample
VALUES ('MAT4977'),
('PPL1007-S'),
('LPL1011'),
('PPL-231-1005'),
('MPL-313-1093-GEN'),
('APL-313-1039-GEN'),
('KPL-231-1005')

select * from @Sample

item----------------result

MAT4977-------------MAT4977
PPL1007-S-----------PPL1007
LPL1011-------------LPL1011
PPL-231-1005--------PPL-231-1005
MPL-313-1093-GEN----MPL-313-1093-GEN
APL-313-1039-GEN----APL-313-1039-GEN
KPL-231-1005--------KPL-231-1005[/code]

2

or how can I convert this excel code into SQL code. This cover all scenario.

=IF(RIGHT(A3,4)="-GEN",LEFT(LEN(A3)-4),IF(RIGHT(A3,2)="-S",LEFT(A3,LEN(A3)-2),A3))

3

Could this work?

CASE 
    WHEN (RIGHT(A3,4)='-GEN' THEN LEFT(LEN(A3)-4) 
    WHEN (RIGHT(A3,2)='-S' THEN LEFT(A3,LEN(A3)-2) 
    ELSE A3 END
4

Your description quoted above and the Excel expression you posted in your second posting don't quite seem to be the same thing. If you really want to replicate the excel expression what @djj55 posted would be the best. But, if your description is what you are trying to implement, you could do the following:

SELECT
	CASE
		WHEN LEN(item)-LEN(REPLACE(item,'-','')) = 1 THEN
			LEFT(item, CHARINDEX('-',item)-1)
		ELSE
			item
		END
FROM
	@Sample;
5 
SELECT item AS item_original,
    CASE WHEN item LIKE '%-%' AND item NOT LIKE '%-%-%' 
         THEN LEFT(item, CHARINDEX('-', item) - 1)
         ELSE item END AS item_new
FROM @Sample
6

Thank you for the reply. I will try this in actual data.

7

Here is the final script I used and its already working. combined all the codes that you've given. thank you guys.

CASE WHEN RIGHT(p.ITEMID,3)='-SR' THEN p.ITEMID WHEN RIGHT(p.ITEMID,2)='-S' THEN LEFT(p.ITEMID, LEN(p.ITEMID) - 2) WHEN RIGHT(p.ITEMID,4)='-GEN' THEN LEFT(p.ITEMID, LEN(p.ITEMID) - 4) WHEN p.ITEMID LIKE '%-%-%' THEN p.ITEMID ELSE LEFT(p.ITEMID,7) END AS ITEMID

8

There's a changes in the requirements, those string with two succeeding two hyphen I would like it to removed from the string. Please see below sample DDL. thanks.

DECLARE	@Sample TABLE
	(
		item nvarchar(20)
	)
INSERT	@Sample
VALUES	('MAT4977'),
('PPL1007-S'),
('LPL1011'),
('PPL-231-1005'),
('MPL-313-1093-GEN'),
('APL-313-1039-GEN'),
('KPL-231-1005')


SELECT item AS item_original,
    CASE WHEN item LIKE '%-%' AND item NOT LIKE '%-%-%' 
         THEN LEFT(item, CHARINDEX('-', item) - 1)
         ELSE item END AS item_new
FROM @Sample

--------------------------------
item_original	|item_new
-------------------------------
MAT4977			|MAT4977
PPL1007-S		|PPL1007
LPL1011			|LPL1011
PPL-231-1005	|PPL1005
MPL-313-1093-GEN|MPL1093-GEN
APL-313-1039-GEN|APL1039-GEN
KPL-231-1005	|KPL1005