Sub String By Character From Table Type

I would like to substring from list of table type. Code is below @Description AS dbo.ListStringTableType And @Description have like list of data below Test+Again Today+Tomorrow

Solution 1:

SQL Server 2016 has the split() function. But, you can instead just do:

select v.col1, v.col2
from t outer apply
     (values (left(col, charindex('+', col) -1),
              stuff(col, 1, charindex('+', col), '')
             )
     ) v(col1, col2);

Solution 2:

You can get the substring using charindex as below.

declare@varvarchar(20)='Test+Again'selectsubstring(@var,1,CHARINDEX('+',@var)-1) as col1
,substring(@var,CHARINDEX('+',@var)+1,len(@var)) as col2

The @var variable should be the column in your case.

Solution 3:

Select left(description,charindex('+', description)-1) as col1, 
right(description,len(description)-charindex('+', description)) as col2

CharIndex will help to find the 1st position of the string we provide, For col1 we require string before string '+', so we are using the 'left' function here. 'left' will fetch the number of letters from the start to the length mentioned(extracted via charindex function, will return the position of the exact string in the given input). Similarly, for col2 we require string after string '+', so we are using the 'right' function here. 'right' will fetch the number of letters from the last to the length mentioned(total length of the string - length extracted via charindex function). For More Info about, Right, Left Function, please use below link.

https://social.technet.microsoft.com/wiki/contents/articles/17948.t-sql-right-left-substring-and-charindex-functions.aspx