How to split dash-separated values in SQL Server?

Question

How do I get only the middle part of the data in my table? I tried the following code, but this only removes the right part... my output should only be middle part.

For instance when I select the data 1-021514-1 the output should be 021514 without the left and right dashes

select LEFT(ticketid, CHARINDEX('-', ticketid + '-') + 4) 
from Table

My Data is:

|TicketID   |
------------
|1-021514-1 |
|10-021514-1|
|2-021514-1 |
|4-021414-1 |

May be you are looking for a split function. [here](http://stackoverflow.com/questions/2647/split-string-in-sql) is an example. — Raging Bull, Feb 15 '14 at 04:56
1-021514 Invalid length parameter passed to the LEFT or SUBSTRING function. i got this error due to one dash. pls suggest me on this — NarasimhaKolla, Aug 12 '20 at 15:22

score 2 · Answer 1 · answered Feb 15 '14 at 05:05

2

Please try:

SELECT
  LEFT(st, CHARINDEX('-', st)-1) TicketID
from
(
    SELECT
        SUBSTRING(TicketID,  CHARINDEX('-',TicketID)+1, 10000) st
    FROM Table
)x

answered Feb 15 '14 at 05:05

TechDo

18,398
3
51
64

score 1 · Answer 2 · answered Feb 15 '14 at 05:11

Try this

with t as (select TicketID  as val)
select t.*,
       LEFT(val, charindex('-', val) - 1),
   SUBSTRING(val, charindex('-', val)+1, len(val) - CHARINDEX('-', reverse(val)) - charindex('-', val)),
       REVERSE(LEFT(reverse(val), charindex('-', reverse(val)) - 1))
from t;

(Or)

Use below Function

CREATE FUNCTION dbo.SplitStrings_CTE(@List nvarchar(max), @Delimiter nvarchar(1))
RETURNS @returns TABLE(val nvarchar(max), [level] int, PRIMARY KEY CLUSTERED([level]))
AS
BEGIN
;WITH cte AS
 (
  SELECT SUBSTRING(@List, 0, CHARINDEX(@Delimiter, @List)) AS val,
         CAST(STUFF (@List + @Delimiter, 1, CHARINDEX(@Delimiter, @List), '') AS nvarchar(max)) AS stval,
         1 AS [level]
  UNION ALL
  SELECT SUBSTRING(stval, 0, CHARINDEX(@Delimiter, stval)),
         CAST(STUFF (stval, 1, CHARINDEX(@Delimiter, stval), '') AS nvarchar(max)),
         [level] + 1
  FROM cte
  WHERE stval != ''
  )
  INSERT @returns
  SELECT REPLACE(val, ' ', '') AS val, [level]
  FROM cte
  RETURN
END

Heena Chhatrala · Answer 3 · 2014-02-15T05:32:23.920

1

hi Ron try this,

   declare @string varchar(25)='1-021514-1'
        declare @val varchar(25)
        SELECT @val= SUBSTRING(@string,  CHARINDEX('-', @string)+1,  ((CHARINDEX('-',@string,(charindex('-',@string)+1))-CHARINDEX('-', @string))-1))
        select @val

edited Feb 15 '14 at 05:32

answered Feb 15 '14 at 05:25

Heena Chhatrala

242
1
8

score 0 · Accepted Answer · answered Feb 15 '14 at 05:04

Try this:

select right(left(ticketid, charindex('-', ticketid, charindex('-', ticketid, 0) + 1) - 1), len(left(ticketid, charindex('-', ticketid, charindex('-', ticketid, 0) + 1) - 1)) - charindex('-', left(ticketid, charindex('-', ticketid, charindex('-', ticketid, 0) + 1) - 1), 0)) from Table

score 0 · Answer 5 · answered Feb 15 '14 at 05:25

0

Try this

SELECT STUFF(
                STUFF(TicketID,1,CHARINDEX('-',TicketID,1),'')
                ,CHARINDEX('-',STUFF(TicketID,1,CHARINDEX('-',TicketID,1),''),1)
                ,LEN(TicketID)
                ,'') 
        from Table1

answered Feb 15 '14 at 05:25

sureshhh

1,196
10
15

How to split dash-separated values in SQL Server?

5 Answers5