[sql] Get everything after and before certain character in SQL Server

I got the following entry in my database:

images/test.jpg

I want to trim the entry so I get: test

So basically, I want everything after / and before .

How can I solve it?

This question is related to sql sql-server

The answer is


I found Royi Namir's answer useful but expanded upon it to create it as a function. I renamed the variables to what made sense to me but you can translate them back easily enough, if desired.

Also, the code in Royi's answer already handled the case where the character being searched from does not exist (it starts from the beginning of the string), but I wanted to also handle cases where the character that is being searched to does not exist.

In that case it acts in a similar manner by starting from the searched from character and returning the rest of the characters to the end of the string.

CREATE FUNCTION [dbo].[getValueBetweenTwoStrings](@inputString 
NVARCHAR(4000), @stringToSearchFrom NVARCHAR(4000), @stringToSearchTo 
NVARCHAR(4000))
RETURNS NVARCHAR(4000)
AS
BEGIN      
DECLARE @retVal NVARCHAR(4000)
DECLARE @stringToSearchFromSearchPattern NVARCHAR(4000) = '%' + 
@stringToSearchFrom + '%'

SELECT @retVal = SUBSTRING (
       @inputString,
       PATINDEX(@stringToSearchFromSearchPattern, @inputString) + LEN(@stringToSearchFrom),
       (CASE
            CHARINDEX(
                @stringToSearchTo,
                @inputString,
                PATINDEX(@stringToSearchFromSearchPattern, @inputString) + LEN(@stringToSearchFrom))
        WHEN
            0
        THEN
            LEN(@inputString) + 1
        ELSE
            CHARINDEX(
                @stringToSearchTo,
                @inputString,
                PATINDEX(@stringToSearchFromSearchPattern, @inputString) + LEN(@stringToSearchFrom))
        END) - (PATINDEX(@stringToSearchFromSearchPattern, @inputString) + LEN(@stringToSearchFrom))
   )
RETURN @retVal
END

Usage:

SELECT dbo.getValueBetweenTwoStrings('images/test.jpg','/','.') AS MyResult

Before

SELECT SUBSTRING(ParentBGBU,0,CHARINDEX('/',ParentBGBU,0)) FROM dbo.tblHCMMaster;

After

SELECT SUBSTRING(ParentBGBU,CHARINDEX('-',ParentBGBU)+1,LEN(ParentBGBU)) FROM dbo.tblHCMMaster

use the following function

left(@test, charindex('/', @test) - 1)

SELECT SUBSTRING('[email protected]',1,(CHARINDEX('@','[email protected]')-1)) Before, RIGHT('[email protected]',(CHARINDEX('@','[email protected]')+1)) After


if Input= pg102a-wlc01s.png.intel.com and Output should be pg102a-wlc01s

we can use below query :

select Substring(pc.name,0,charindex('.',pc.name,0)),pc.name from tbl_name pc


----select characters before / including /

select SUBSTRING ('abcde/wxyz',0,CHARINDEX('/','abcde/wxyz')+1)

--select characters after / including /

select SUBSTRING('abcde/wxyz',CHARINDEX('/','abcde/wxyz'),LEN('abcde/wxyz')) 

I just did this in one of my reports and it was very simple.

Try this:

=MID(Fields!.Value,8,4)

Note: This worked for me because the value I was trying to get was a constant not sure it what you are trying to get is a constant as well.


I have made a method which is much more general :

so :

DECLARE @a NVARCHAR(MAX)='images/test.jpg';


 --Touch here
DECLARE @keysValueToSearch NVARCHAR(4000) = '/'
DECLARE @untilThisCharAppears NVARCHAR(4000) = '.'
DECLARE @keysValueToSearchPattern NVARCHAR(4000) = '%' + @keysValueToSearch + '%'


 --Nothing to touch here     
SELECT SUBSTRING(
           @a,
           PATINDEX(@keysValueToSearchPattern, @a) + LEN(@keysValueToSearch),
           CHARINDEX(
               @untilThisCharAppears,
               @a,
               PATINDEX(@keysValueToSearchPattern, @a) + LEN(@keysValueToSearch)
           ) -(PATINDEX(@keysValueToSearchPattern, @a) + LEN(@keysValueToSearch))
       )

I know this has been a while.. but here is an idea

declare @test varchar(25) = 'images/test.jpg'

select
 @test as column_name
 , parsename(replace(@test,'/','.'),1) as jpg
 ,parsename(replace(@test,'/','.'),2) as test
  ,parsename(replace(@test,'/','.'),3) as images

I got some invalid length errors. So i made this function, this should not give any length problems. Also when you do not find the searched text it will return a NULL.

CREATE FUNCTION [FN].[SearchTextGetBetweenStartAndStop](@string varchar(max),@SearchStringToStart varchar(max),@SearchStringToStop varchar(max))

RETURNS varchar(max)

BEGIN


    SET @string =    CASE 
                         WHEN CHARINDEX(@SearchStringToStart,@string) = 0
                           OR CHARINDEX(@SearchStringToStop,RIGHT(@string,LEN(@string) - CHARINDEX(@SearchStringToStart,@string) + 1 - LEN(@SearchStringToStart))) = 0
                         THEN NULL
                         ELSE SUBSTRING(@string
                                       ,CHARINDEX(@SearchStringToStart,@string) + LEN(@SearchStringToStart) + 1
                                       ,(CHARINDEX(@SearchStringToStop,RIGHT(@string,LEN(@string) - CHARINDEX(@SearchStringToStart,@string) + 1 - LEN(@SearchStringToStart)))-2)     
                                       )
                     END


    RETURN @string

END

Simply Try With LEFT ,RIGHT ,CHARINDEX

select 
LEFT((RIGHT(a.name,((CHARINDEX('/', name))+1))),((CHARINDEX('.', (RIGHT(a.name, 
                     ((CHARINDEX('/', name))+1)))))-1)) splitstring,
a.name      
from 
   (select 'images/test.jpg' as name)a

You can try this:

Declare @test varchar(100)='images/test.jpg'
Select REPLACE(RIGHT(@test,charindex('/',reverse(@test))-1),'.jpg','')

Below query gives you data before '-' Ex- W12345A-4S

SELECT SUBSTRING(Column_Name,0, CHARINDEX('-',Column_Name))  as 'new_name'
from [abc].

Output - W12345A


 declare @T table
  (
  Col varchar(20)
  )



  insert into @T 
  Select 'images/test1.jpg'
  union all
  Select 'images/test2.png'
  union all
  Select 'images/test3.jpg'
  union all
  Select 'images/test4.jpeg'
  union all
  Select 'images/test5.jpeg'

 Select substring( LEFT(Col,charindex('.',Col)-1),charindex('/',Col)+1,len(LEFT(Col,charindex('.',Col)-1))-1 )
from @T

Inspired by the work of Josien, I wondered about a simplification.

Would this also work? Much shorter:

SELECT SUBSTRING(col, CHARINDEX ('/', col) + 1, CHARINDEX ('.', col) - CHARINDEX ('/', col) - 1);

(I can't test right now because of right issues at my company SQL server, which is a problem in its own right)