Splitting string after specific character in sql

Question

I have for example this string name-surname-15A-15B and for example if I want to get the last segment(which is 15B) I can use the following query:

Select right(Code, charindex('-', reverse(Code)) - 1) as segment From MyTable

But how can I get string after specific dash for example the second one which will return 15A-15B ?

Attention: Although this is a duplicate question one warning: The linked questions provides some very old and outdated answers. They were good when they were written, but it is a good idea to search for newer / more active answers... — Shnugo, Mar 06 '18 at 14:24

score 3 · Answer 1 · answered Mar 06 '18 at 14:23

3

Just another option is ParseName() provided the segments don't exceed 4

Example

Declare @S varchar(50)='name-surname-15A-15B'

Select ParseName(Replace(@S,'-','.'),1)

Returns

15B

For last two positions

Declare @S varchar(50)='name-surname-15A-15B'

Select ParseName(Replace(@S,'-','.'),2)
      +'-' 
      +ParseName(Replace(@S,'-','.'),1)

Returns

15A-15B

answered Mar 06 '18 at 14:23

John Cappelletti

63,100
6
36
57

1

You've done it again :-D – Shnugo Mar 06 '18 at 14:26

score 2 · Answer 2 · answered Mar 06 '18 at 14:15

2

Well you can do it by using something like this.

SELECT SUBSTRING( string , LEN(string) -  CHARINDEX('-',REVERSE(string)) + 2  , LEN(string)  ) FROM SAMPLE;

Where string is the string you want to split.

answered Mar 06 '18 at 14:15

asad ullah

21
2

Larnu · Answer 3 · 2018-03-06T14:32:13.053

Provided that there will be 3, and only 3, hyphens (-) in the value of Code, you could do:

SELECT Code,
       RIGHT(Code, LEN(Code) - CI1.CI) AS C1,
       RIGHT(Code, LEN(Code) - CI2.CI) AS C2, --One you're after
       RIGHT(Code, LEN(Code) - CI3.CI) AS C3
FROM (VALUES ('name-surname-15A-15B')) V(Code)
     CROSS APPLY (VALUES(CHARINDEX('-',V.Code))) CI1(CI)
     CROSS APPLY (VALUES(CHARINDEX('-',V.Code,CI1.CI+1))) CI2(CI)
     CROSS APPLY (VALUES(CHARINDEX('-',V.Code,CI2.CI+1))) CI3(CI);

You can easily expand this for more than that; however, if you have quite a lot of delimiters, I'd suggest looking into a string splitter, and then using FOR XML PATHto concatenate the string back together. Edit: Or STRING_AGGof you're using SQL Server 2017.

I'd love to know what the downvote was for. Feedback is always appreciated. :) — Larnu, Mar 07 '18 at 08:58

Splitting string after specific character in sql

3 Answers3