sql server all characters to right of first hyphen

Question

In SQL Server 2014, how can I extract all characters to the right of the first hyphen in a field where the first hyphen will have many combinations following it.

example 1:

Aegean-1GB-7days-COMP

desired result:

1GB-7days-COMP

example 2:

Aegean-SchooliesSpecial-7GB

desired result:

SchooliesSpecial-7GB

example 3:

AkCityOaks-1Day-3GB

desired result:

1Day-3GB

Possible duplicate of: http://stackoverflow.com/questions/11010453/get-everything-after-and-before-certain-character-in-sql-server — J.S. Orris, Jul 23 '15 at 14:35

score 4 · Answer 1 · answered Jul 23 '15 at 14:45

4

Using CHARINDEX AND SUBSTRING would work:

DECLARE @HTXT as nvarchar(max)
SET @HTXT='lkjhgf-wtrfghvbn-jk87fry--jk'
SELECT SUBSTRING(@HTXT, CHARINDEX('-', @HTXT) + 1, LEN(@HTXT))

Result: wtrfghvbn-jk87fry--jk

answered Jul 23 '15 at 14:45

Paul van der Elst

41
2

score 1 · Answer 2 · answered Jul 23 '15 at 14:38

You can use a combination of CharIndex and 'SubString' to get the desired result.

When you do this, you will get the location of the first hyphen starting from the first character.

CharIndex ('Aegean-1GB-7days-COMP', '-', 1)

Then cutting the string is easy

Select     
    SubString (
      'Aegean-1GB-7days-COMP', 
      CharIndex ('-', 'Aegean-1GB-7days-COMP', 1) + 1,
      Len('Aegean-1GB-7days-COMP') - CharIndex ('-', 'Aegean-1GB-7days-COMP', 1)
    )

Since your data is most likely in a column, I would change this to

Select     
    SubString (
      YourColumnName, 
      CharIndex ('-', YourColumnName, 1) + 1,
      Len(YourColumnName) - CharIndex ('-', YourColumnName, 1)
    )
From YourTableName

If you want to match -- instead of -, then look at PatIndex`

Read Here about CharIndex

Read Here about PatIndex

Read Here about SubString

Only problem is when I plug into a view I get "function argument count error" — Jeff Palmer, Jul 23 '15 at 15:13
It appears that a three-argument CHARINDEX() function can be entered into the SQL Pane of the View Designer. — Jeff Palmer, Jul 23 '15 at 15:18
Easy solution, write the view yourself instead of using that awful view designer. :D — Sean Lange, Jul 23 '15 at 15:21

M. Grue · Answer 3 · 2015-07-23T14:44:22.533

0

Hi you can use PATINDEX and SUBSTRING like this:

DECLARE @Text NVARCHAR(4000)
DECLARE @StartPos int

SET @StartPos = PATINDEX('%-%',@Text) + 1

RETURN SUBSTRING(@Text,@StartPos,LEN(@Text)-@StartPos)

Or in one:

SUBSTRING([Text],PATINDEX('%A%',[Text]) + 1, LEN([Text]) - PATINDEX('%A%',[Text]) + 1)

edited Jul 23 '15 at 14:44

answered Jul 23 '15 at 14:39

M. Grue

331
2
11

sql server all characters to right of first hyphen

3 Answers3