Get everything after and before certain character in SQL Server

Question

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?

Is there any specific reason not to do it in the client language (i.e. .NET, Java, PHP or whatever...)? — Branko Dimitrijevic, Jun 13 '12 at 07:57
And would you be so kind to share it with us? Databases are for storing the data, not displaying it in any particular format. Your question may or may not be indicative of a deeper problem, and for us to be able to judge that, we need a reason. — Branko Dimitrijevic, Jun 13 '12 at 08:15

score 102 · Answer 1 · edited Oct 05 '19 at 20:10

102

use the following function

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

edited Oct 05 '19 at 20:10

QMaster

3,743
3
43
56

answered May 31 '14 at 08:20

baljeet Singh

1,037
1
7
2

8

Did you mean `-1` as opposed to `-2` ? – James McClelland Jul 11 '14 at 10:23
7

While this does not answer the OPs 2 requirements, it is a nice CLEAN method of answering his 1st - and the more common request. (It is EXACTLY what I was looking for) – kiltannen Jun 06 '18 at 03:41
4

It fails if character is not presented there – Zuwaib Ansari Feb 18 '20 at 11:05
4

LEFT(@test, ISNULL(NULLIF(CHARINDEX('/', @test)-1, -1), LEN(@test))); – meir Mar 24 '21 at 07:16
1

What will be the option to pass TEXT datatype in left() instead of VARCHAR? – Rahul Mankar Apr 27 '22 at 12:04

score 76 · Accepted Answer · edited Apr 19 '22 at 12:21

76

If you want to get this out of your table using SQL, take a look at the following functions that will help you: SUBSTRING and CHARINDEX. You can use those to trim your entries.

A possible query will look like this (where col is the name of the column that contains your image directories:

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

Bit of an ugly beast. It also depends on the standard format of 'dir/name.ext'.

Edit:
This one (inspired by praveen) is more generic and deals with extensions of different length:

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

edited Apr 19 '22 at 12:21

Rory

40,559
52
175
261

answered Jun 13 '12 at 08:01

Josien

13,079
5
36
53

The query however wont work if different extension (.jpeg,png,jpg) are inserted into the column .Never the less it works absolutely fine with the OP's sample data – praveen Jun 13 '12 at 08:42
@praveen: it works on extensions of different length - as long as they start with a dot. – Josien Jun 13 '12 at 08:47
Consider an input like 'images/test.jpeg .Your sql query retrieves est as the result – praveen Jun 13 '12 at 08:54

score 38 · Answer 3 · edited Jan 04 '17 at 13:37

38

Before

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

After

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

edited Jan 04 '17 at 13:37

Matthew Verstraete

6,335
22
67
123

answered Jan 04 '17 at 13:13

Ankur Shah

467
5
3

score 14 · Answer 4 · edited Aug 09 '19 at 19:40

14

----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'))

edited Aug 09 '19 at 19:40

DxTx

3,049
3
23
34

answered Jan 08 '19 at 11:33

Asad Naeem

558
8
14

praveen · Answer 5 · 2012-06-13T08:43:19.833

6

 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

edited Jun 13 '12 at 08:43

answered Jun 13 '12 at 08:38

praveen

12,083
1
41
49

score 5 · Answer 6 · answered Sep 03 '16 at 07:58

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))
       )

This is a good example and. really thank you for this. If there's a plan to use this code in function or SP, I recommend checking if there are values in input string: DECLARE FirstAppearanceIndex [int] = CHARINDEX(KeysValueToSearch, InputString); DECLARE SecondAppearanceIndex [int] = CHARINDEX(UntilThisCharAppears, InputString); IF (FirstAppearanceIndex <= 0 OR SecondAppearanceIndex <= 0 OR FirstAppearanceIndex > SecondAppearanceIndex) BEGIN RETURN ''; END — GenTech, Jul 06 '20 at 13:35

score 4 · Answer 7 · edited May 21 '22 at 18:21

4

SELECT Substring('ravi1234@gmail.com', 1, ( Charindex('@', 'ravi1234@gmail.com')
                                            - 1 ))
       Before,
       RIGHT('ravi123@gmail.com', ( Charindex('@', 'ravi123@gmail.com') + 1 ))
       After

edited May 21 '22 at 18:21

RF1991

2,037
4
8
17

answered Aug 20 '20 at 05:38

Ravi Sharma

362
1
5

score 3 · Answer 8 · edited Aug 09 '19 at 20:39

3

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.

edited Aug 09 '19 at 20:39

DxTx

3,049
3
23
34

answered Apr 28 '15 at 14:18

Tamara

39
1

1

`MID()` is not a TSQL function. You're presumably thinking of the Visual BASIC dialect available in Report Builder, but that's not what this is about. – underscore_d Feb 22 '18 at 11:50

score 2 · Answer 9 · answered Jul 26 '17 at 04:17

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

score 1 · Answer 10 · answered Apr 19 '18 at 18:50

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

score 1 · Answer 11 · answered May 26 '20 at 08:05

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

score 1 · Answer 12 · edited May 21 '22 at 18:28

1

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

edited May 21 '22 at 18:28

RF1991

2,037
4
8
17

answered Feb 12 '21 at 05:54

Pankti Shah

189
1
6

score 1 · Answer 13 · answered Apr 12 '22 at 13:25

If there are more than one or none occurences of given character use this:

DECLARE @rightidx int = CASE
    WHEN 'images/images/test.jpg' IS NULL OR (CHARINDEX('.', 'images/images/test.jpg')) <= 0 THEN LEN('images/images/test.jpg')
    ELSE  (CHARINDEX('.', REVERSE('images/images/test.jpg')) - 1)
END

SELECT RIGHT('images/images/test.jpg', @rightidx)

score 0 · Answer 14 · edited Aug 09 '19 at 20:41

0

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

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

Output - W12345A

edited Aug 09 '19 at 20:41

DxTx

3,049
3
23
34

answered Aug 20 '17 at 11:21

Pradeep Thakur

11
2

score 0 · Answer 15 · edited Apr 03 '19 at 13:57

0

You can try this:

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

edited Apr 03 '19 at 13:57

Samer Abu Gahgah

751
1
9
18

answered Apr 03 '19 at 13:08

Piyush jain

1

Please try to explain your solution a little bit, and why it would work. In order to let more people benefit from it. – Samer Abu Gahgah Apr 03 '19 at 13:26

score 0 · Answer 16 · answered Nov 07 '19 at 15:49

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)

score 0 · Answer 17 · answered Feb 04 '20 at 10:56

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

score 0 · Answer 18 · answered Feb 14 '22 at 18:41

declare @searchStart nvarchar(100) = 'search ';
declare @searchEnd nvarchar(100) = ' ';
declare @string nvarchar(4000) = 'This is a string to search (hello) in this text ';

declare @startIndex int = CHARINDEX(@searchStart, @string,0) + LEN(@searchStart);
declare @endIndex int = CHARINDEX(@searchEnd, @string, @startIndex + 1);
declare @length int = @endIndex - @startIndex;
declare @sub nvarchar(4000) = SUBSTRING(@string, @startIndex, @length)

select @startIndex, @endIndex, @length, @sub

This is a little more legible than the one-liners in this answer which specifically answer the question, but not in a generic way that would benefit all readers. This could easily be made into a function as well with a slight modification.

Daniel L. VanDenBosch · Answer 19 · 2022-04-14T18:53:12.127

This was the approach I took.

    CREATE FUNCTION dbo.get_text_before_char(@my_string nvarchar(255),@my_char char(1))
    RETURNS nvarchar(255)
    AS
        BEGIN;
            return IIF(@my_string LIKE '%' + @my_char + '%',left  (@my_string, IIF(charindex(@my_char, @my_string) - 1<1,1,charindex(@my_char, @my_string) - 1)),'');
        END;
    
    CREATE FUNCTION dbo.get_text_after_char(@my_string nvarchar(255),@my_char char(1))
        RETURNS nvarchar(255)
    AS
    BEGIN;
       return IIF ( @my_string LIKE '%' + @my_char + '%' ,RIGHT ( @my_string , IIF ( charindex ( @my_char ,reverse(@my_string) )-1 < 1 ,1 ,charindex ( @my_char ,reverse(@my_string) )-1 ) ) , '' )
    END;
    
    SELECT
          dbo.get_text_before_char('foo-bar','-')
        , dbo.get_text_after_char('foo-bar','-')

score 0 · Answer 20 · edited May 22 '22 at 00:18

0

declare @test varchar(100)='images/test.jpg'
select right(left(@test, charindex('.', @test) - 1),4)

edited May 22 '22 at 00:18

Henry Ecker

34,399
18
41
57

answered May 22 '22 at 00:12

Federer-57

11
2

While this might answer the question, if possible you should [edit] your answer to include a short explanation of *how* this script answers the question. This helps to provide context, and makes your answer much more useful for future readers. – Hoppeduppeanut May 27 '22 at 04:54

score 0 · Answer 21 · answered Apr 05 '23 at 00:45

0

Use the following for the first part:

SELECT LEFT(@test, CASE CHARINDEX('/', @test)-1 WHEN -1 THEN LEN(@test) ELSE CHARINDEX('/', @test)-1 END)

answered Apr 05 '23 at 00:45

Hamid Heydarian

802
9
16

score 0 · Answer 22 · edited Apr 17 '23 at 11:01

0

I had the same problem today. I'm using snowflake and this worked fine for me:

substring(column_name, charindex('start pattern', column_name)+ start pattern length, charindex('end pattern', column_name)-charindex('start pattern', column_name)-start pattern lenght)

edited Apr 17 '23 at 11:01

borchvm

3,533
16
44
45

answered Apr 13 '23 at 01:10

absinth

1

rodolfocolon · Answer 23 · 2023-08-21T10:37:21.617

0

I found a simply Query to arpoach it, This is clean Tydy and no long to run and understand

My string

B:\Pato\SubPAto\SUBUBPATO\El Patito Feo.prf

Result:

El Patito Feo

Query:

SELECT ScanFileName,    
    SUBSTRING(ScanFileName, LEN(ScanFileName) - CHARINDEX('\',REVERSE(ScanFileName)) + 2, 
    CHARINDEX ('.', ScanFileName)-(LEN(ScanFileName) - CHARINDEX('\',REVERSE(ScanFileName)) + 2)
    )
  FROM Table

edited Aug 21 '23 at 10:37

answered Aug 17 '23 at 15:50

rodolfocolon

1
2

1

Thank you for your interest in contributing to the Stack Overflow community. This question already has quite a few answers—including one that has been extensively validated by the community. Are you certain your approach hasn’t been given previously? **If so, it would be useful to explain how your approach is different, under what circumstances your approach might be preferred, and/or why you think the previous answers aren’t sufficient.** Can you kindly [edit] your answer to offer an explanation? – Jeremy Caney Aug 19 '23 at 01:08

Get everything after and before certain character in SQL Server

23 Answers23

Linked