Click here to Skip to main content
14,334,974 members
Rate this:
Please Sign up or sign in to vote.
See more:
I want to fetch a substring from a string in sql server

E:\VORDERINGSSTAAT\1_DDP12101PME.pdf

the above path is stored in sql table, i want to fetch 1_DDP12101PME.pdf using sql query from above path



Anyone help plz
Posted
Rate this:
Please Sign up or sign in to vote.

Solution 2

DECLARE @file varchar(255)
DECLARE @charPosition int

SET @file = 'E:\VORDERINGSSTAAT\1_DDP12101PME.pdf'
SET @charPosition = (Len(@file)) - CharIndex('\', Reverse(@file))

SELECT SUBSTRING(@file, @charPosition+2, (LEN(@file) - @charPosition)+1)


returns: 1_DDP12101PME.pdf

Same code, but in one query:
SELECT SUBSTRING(@file, ((Len(@file)) - CharIndex('\', Reverse(@file)))+2, (LEN(@file) - ((Len(@file)) - CharIndex('\', Reverse(@file))))+1)
   
Rate this:
Please Sign up or sign in to vote.

Solution 3

SELECT Reverse(SUBSTRING(REVERSE(FilePath),0,CHARINDEX('\', REVERSE(FilePath),0)))
 
FROM Files
   
Rate this:
Please Sign up or sign in to vote.

Solution 1

hey kishore,
use split() function.

like this:
string path= "E:\VORDERINGSSTAAT\1_DDP12101PME.pdf";
string [] pdffilename = path.split('\');
now "1_DDP12101PME.pdf" this value comes into string array.

now get your value like this:
string filename = pdffilename[pdffilename.Length-1];
in filename variable your value are coming... try this in your application hope it will work for you...
all the best.....
   
Rate this:
Please Sign up or sign in to vote.

Solution 4

If you want do it directly from SQL, try the following query:

SELECT SUBSTRING(
'E:\asdas\sdfae\qargqerg\eqrger.pdf',
(LEN('E:\asdas\sdfae\qargqerg\eqrger.pdf') - CHARINDEX('\', REVERSE('E:\asdas\sdfae\qargqerg\eqrger.pdf'), 0)) + 2,
LEN('E:\asdas\sdfae\qargqerg\eqrger.pdf'));


How this works:
- You want to only extract the last part of the path (i.e. filename), so you'll obviously need a substring function.
Substring works like this: Substring(string, start_index, end_index), where:
- string is the full word that you want to get a part out of
- start_index is the 0-based position of the first letter of the new string
- end_index is the position of the last letter of the new string. So, if we have:
Substring('Get me some codez', 12, 4) will return code.

The second parameter in the query above is trickier. That one will take the string, reverse it, and get the first position of \ (that's because SQL Server doesn't have the LastIndexOf function)

The third parameter is the length of the return string (which is the full string's length, because, for instance you can have no backslashes)

Also, I suggest you read some documentation on string functions:
- Substring[^]
- CharIndex[^]
- Reverse[^]
   
v3

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100