Menu
I have a table with a column which contains strings like below.
RTSPP_LZ_AEN
RTSPP_LZ_CPS
RTSPP_LZ_HOUSTON
RTSPP_LZ_LCRA
RTSPP_LZ_NORTH
RTSPP_LZ_RAYBN
RTSPP_LZ_SOUTH
RTSPP_LZ_WEST
RTSPP_BTE_CC1
RTSPP_BTE_PUN1
RTSPP_BTE_PUN2
I need to get the substring from the second occurrence of _ till the end of string and as you can see the substring is not of fixed length. The first part is not always fixed it can change. As of now I am using the following code to achieve it.
SELECT SUBSTRING([String],CHARINDEX('_',[String],(CHARINDEX('_',[String])+1))+1,100)
FROM [Table]
As you can see I am taking an arbitrary large value as the length to take care of variable length. Is there a better way of doing it?
934
Dynamically locate the starting and end character Using the SQL Server Substring function, the numeric sub-set of a string from the column col is transformed using CHARINDEX. You can also use the SQL string function PATINDEX to find the initial position and length parameter of the SQL SUBSTRING function.
CHARINDEX function in SQL queries It works reverse to the SUBSTRING function. The substring() returns the string from the starting position however the CHARINDEX returns the substring position. In the below example, we retrieve the position of substring SQLSHACK.COM using the CHARINDEX.
There is no difference at all between left and substring because left is translated to substring in the execution plan. Save this answer.
The SUBSTRING function requires two arguments and can take a third. The first argument is the string to be found and it can be stated either as a string or as a column. The second argument is the placement in the string of the first character to be returned.
This seems nice to me:
declare @yourvariable nvarchar(max)
set @yourvariable=(Select
(SELECT distinct ','+yourcolumn
from yourtable
for xml path('')) )
select substring(@yourvariable,2,len(@yourvariable))
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
