substring in sql to get last text after dash

Question

Hi Team, ex: 1) queueaccuracy - workflow - bodytext 2) queueaccuracy - offsiteson 3) adds accuracy - video - image output: bodytext offsiteson image Please help to to get text to the right after the last dash . Thanks in advance.

Ecco Suprastyo · Answer

Hi sai, You can try use STRING_SPLIT in SQL server, like below : SELECT TOP 1 value FROM STRING_SPLIT( ' queueaccuracy - workflow - bodytext' , '-' ) order by value asc The result is : bodytext

Priyanka Singh · Answer

By searching for the location of the last occurrence of the delimiter '-' we can easily strip off just the output using the RIGHT function. The following SQL does just that : DECLARE @FullPath VARCHAR (200) SET @FullPath = 'queueaccuracy - workflow - bodytext' SELECT RIGHT (@FullPath , CHARINDEX ( '-' ,REVERSE(@FullPath))-1) DECLARE @FullPath1 VARCHAR (200) SET @FullPath1 = 'queueaccuracy - offsiteson' SELECT RIGHT (@FullPath1 , CHARINDEX ( '-' ,REVERSE(@FullPath1))-1) DECLARE @FullPath2 VARCHAR (200) SET @FullPath2 = 'adds accuracy - video - image' SELECT RIGHT (@FullPath2 If yourun this query it returns just the output what you expect.

Jignesh Kumar · Answer

Hi Sai, Please use below query which will resolve your problem, declare @str varchar (100) set @str= 'queueaccuracy - workflow - bodytext' SELECT @str, RIGHT (@str,CHARINDEX( '-' ,REVERSE(@str))-1) file_name -- Here you need you replace @str with your column name -- SELECT ColumnName,RIGHT(ColumnName,CHARINDEX('-',REVERSE(ColumnName))-1) file_name from YourTableName

Joginder Banger · Answer

Hi Sai, I made a common function for reslove your problem. Just look and let me know . CREATE FUNCTION Function_Name(@Parameter_Name varchar (100),@Delimator varchar (1)) RETURNS varchar ( max ) AS BEGIN declare @TempValue varchar ( max ) SELECT @TempValue= RIGHT (@Parameter_Name,CHARINDEX (@Delimator ,REVERSE(@Parameter_Name))-1 ) return @TempValue END create table #tempdata ( Name varchar ( max )) insert into #tempdata values ( 'adds accuracy - video - image' ) select dbo.Function_name( Name , '-' ) from #tempdata Output:Image

substring in sql to get last text after dash

Answers (4)