CODEWITHSUNDEEP
sqlsql-server
Jeff Palmer
Jeff Palmer

Reputation: 21

sql server all characters to right of first hyphen

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

Upvotes: 1

Views: 4537

Answers (3)

Paul van der Elst
Paul van der Elst

Reputation: 41

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

Upvotes: 4

M. Grue
M. Grue

Reputation: 331

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)

Upvotes: 0

Raj More
Raj More

Reputation: 48048

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

Upvotes: 1

Related Questions