CODEWITHSUNDEEP
sqlsql-server
elunap
elunap

Reputation: 841

Get substring after and before '-' character

I have this kind of strings in a table:

What I'm trying to do is to get only the middle part, for example:

Basically the substring after and before the '-' Char

I tried this (from an example in here): 

SELECT SUBSTRING(Product.Name, LEN(LEFT(Product.[Name], CHARINDEX ('-', Product.[Name]))) + 1, LEN(Product.[Name]) - LEN(LEFT(Product.[Name], CHARINDEX ('-', Product.[Name]))) - LEN(RIGHT(Product.[Name], LEN(Producto.[Name]) - CHARINDEX ('-', Product.[Name]))) - 1)
FROM Product

But it gives me this error:

[42000] [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid length parameter passed to the LEFT or SUBSTRING function. (537)

Being honest, I don't know how to solve the error because I don't understand the solution from the example. what can I do?

Thanks.

Upvotes: 3

Views: 11537

Answers (3)

Andrew P Smith
Andrew P Smith

Reputation: 11

This will work to find the middle part on older SQL Servers up to and including 2008.

DECLARE @SomeText VARCHAR(100) = 'AM-75/86-650'
SELECT LEFT(RIGHT(@SomeText, LEN(@SomeText) - CHARINDEX('-', @SomeText)),  
           CHARINDEX('-', RIGHT(@SomeText, LEN(@SomeText) - CHARINDEX('-', @SomeText)))-1)

Returns: '75/86'

Upvotes: 0

Forty3
Forty3

Reputation: 2229

Use SUBSTRING with start and lengths calculated based on position of the '-':

SELECT SUBSTRING('ABC-65-DEF', 
                 CHARINDEX('-', 'ABC-65-DEF') + 1, 
                 CHARINDEX('-', 'ABC-65-DEF', CHARINDEX('-', 'ABC-65-DEF') + 1) - CHARINDEX('-', 'ABC-65-DEF') - 1)

Basically, it finds the first instance of the '-' (CHARINDEX('-', 'ABC-65-DEF')) and then the second instance of '-' (CHARINDEX('-', 'ABC-65-DEF', CHARINDEX('-', 'ABC-65-DEF') + 1)) and grabs the substring inbetween.

Upvotes: 1

John Cappelletti
John Cappelletti

Reputation: 81950

One option is ParseName()

Example

Declare @S varchar(max)='AM-65-800'

Select parsename(replace(@S,'-','.'),2)

Returns

65

Upvotes: 5

Related Questions