substring in sql to get last text after dash

Sai Reddy

6y1.5k

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.

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Ecco Suprastyo6y ago
Accepted 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
0
Jignesh Kumar6y ago
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
0
Joginder Banger6y ago
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
0
Priyanka Singh6y ago
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.
0