CODEWITHSUNDEEP
sql-servert-sql
Shabbaranks
Shabbaranks

Reputation: 147

SQL Select statement until a character

I'm looking to extract all the text up until a '\' (backslash).

The substring is required to remove all proceeding characters (17 in total) and so I would like to return all after the 17th until it comes across a backslash.

I've tried using charindex but it doesn't seem to stop at the \ it returns characters afterward. My code is as follows

SELECT path, substring(path,17, CHARINDEX('\',Path)+ LEN(Path)) As Data
  FROM [Table].[dbo].[Projects]
  WHERE Path like '\ENQ%\' AND 
  Deleted = '0'

Example

The below screen shot shows the basic query and result i.e the whole string Image 1

I then use substring to remove the first X characters as there will always be the same amount of proceeding characters

enter image description here

But what Im actually after is (based on the above result) the "Testing 1" "Testing 2" and "Testing ABC" section

Upvotes: 0

Views: 537

Answers (1)

TheGameiswar
TheGameiswar

Reputation: 28930

The substring is required to remove all proceeding characters (17 in total) and so I would like to return all after the 17th until it comes across a backslash.

select 
substring(path,17,CHARINDEX('\',Path)-17)
from
table

To overcome Invalid length parameter passed to the LEFT or SUBSTRING function error, you can use CASE 

 select 
    substring(path,17,
   CASE when  CHARINDEX('\',Path,17)>0
        Then  CHARINDEX('\',Path)-17)
        else VA end
           )
    from
    table

Upvotes: 1

Related Questions