CODEWITHSUNDEEP
sql
Jerry Trac
Jerry Trac

Reputation: 367

SQL Replace string in path

How would I replace everything before "Test" with a blank? The amount of subfolders in front of "Test" may vary.

C:\aaa\bbb\test\ccc\ddd

I would like it to be:

test\ccc\ddd

Upvotes: 0

Views: 217

Answers (3)

Art
Art

Reputation: 5792

This query should work in any SQL - you may add syntatic fixes if required. In Oracle it would be SUBSTR/INSTR for example. 

SELECT SUBSTR(str, start_pos) final_str
FROM
(
SELECT 'C:\aaa\bbb\test\ccc\ddd' as str 
     , INSTR('C:\aaa\bbb\test\ccc\ddd', 'test', 1) as start_pos
  FROM dual
)
/

SQL>

FINAL_STR
-------------
test\ccc\ddd

Upvotes: 0

deeg
deeg

Reputation: 528

Solution in MSSQL, if you're using another RDMBS I'm sure they have equivalent functions for PATINDEX/SUBSTRING. 

DECLARE @Path VARCHAR(8000)
        ,@Find VARCHAR(128)

SET @Path = 'C:\aaa\bbb\test\ccc\ddd'
SET @Find = 'Test\'

SELECT SUBSTRING(@Path,PATINDEX('%'+@Find+'%',@Path),LEN(@Path))

http://sqlfiddle.com/#!3/d41d8/8576

Upvotes: 2

Luis Tellez
Luis Tellez

Reputation: 2983

You can get the index of the string Text in your String, and get a substring from there to the end.

Upvotes: 1

Related Questions