Get a Substring in SQL between two characters and remove all white spaces

Question

I've a strings such as:

Games/Maps/MapsLevel1/Level 1.swf
Games/AnimalWorld/Animal1.1/Level 1.1.swf
Games/patterns and spatial understanding/Level 13.5/Level 13.5.swf

I want to get only file name without its extension(String After last Slash and before Last dot), i.e Level 1 and Level 1.1 and Level 13.5, Even I want to remove all the white spaces and the final string should be in lower case i.e the final output should be

level1
level1.1
level13.5 and so on..

I tried following query but i got Level 1.swf, How do i change this Query?

SELECT SUBSTRING(vchServerPath, LEN(vchServerPath) - CHARINDEX('/', REVERSE(vchServerPath)) + 2, LEN(vchServerPath)) FROM Games

This is what you are looking for : [Parse file name and path from full path in SQL Query.](http://stackoverflow.com/questions/19505875/parse-file-name-and-path-from-full-path) — Atanu Roy, Apr 10 '14 at 10:20
I'm able to get the file name with its extension but here I need with extension, as I've mentioned above. I didn't get how to remove the extension from the file name. — Sushil, Apr 10 '14 at 10:34

score 1 · Accepted Answer · edited Apr 10 '14 at 11:47

SELECT (left((Path), LEN(Path) - charindex('.', reverse(Path))))
FROM 
(
    SELECT SUBSTRING(vchServerPath, 
                     LEN(vchServerPath) - CHARINDEX('/', REVERSE(vchServerPath)) + 2, 
                     LEN(vchServerPath)) Path
    FROM Games
) A

This would work, I kept your inner substring which got you part way and I added the stripping of the dot.

I have included a sql fiddle link for you to see it in action sql fiddle

Edited: Following will remove the white space and returns lower case...

SELECT REPLACE(LOWER((left((Path), LEN(Path) - charindex('.', reverse(Path))))), ' ', '')
FROM 
(
    SELECT SUBSTRING(vchServerPath, 
                     LEN(vchServerPath) - CHARINDEX('/', REVERSE(vchServerPath)) + 2, 
                     LEN(vchServerPath)) Path
    FROM Games
) A

Edited to just give you the stripped Data. – TYY Apr 10 '14 at 11:38 — TYY, Apr 10 '14 at 11:38

shree.pat18 · Answer 2 · 2014-04-10T10:50:41.453

0

Try this:

select
 case 
 when vchServerPath is not null
 then reverse(replace(substring(reverse(vchServerPath),charindex('.',reverse(vchServerPath))+1, charindex('/',reverse(vchServerPath))-(charindex('.',reverse(vchServerPath))+1)),' ',''))
 else ''
end

edited Apr 10 '14 at 10:50

answered Apr 10 '14 at 10:36

shree.pat18

21,449
3
43
63

I tried but getting error: `Invalid length parameter passed to the LEFT or SUBSTRING function.` – Sushil Apr 10 '14 at 10:40
It works for the samples you've shown us as can be seen here: http://rextester.com/XLTR59384. Is there any case where the format is not like any of the samples? – shree.pat18 Apr 10 '14 at 10:43
There are only three formats I can say, i.e it can either be as `/Games/Fractions and Decimals/Level 21.11/Level 21.11.swf` or `Games/PlantWorld/Level 4.7f.2/Level 4.7.swf` or null – Sushil Apr 10 '14 at 10:48

Rahul · Answer 3 · 2014-04-10T11:09:01.020

0

This should work fine; with extension removed.

select 
REVERSE(
SUBSTRING(
reverse('Games/patterns and spatial understanding/Level 13.5/Level 13.5.swf'),
5,
(charindex('/',
reverse('Games/patterns and spatial understanding/Level 13.5/Level 13.5.swf')) - 5)
))

edited Apr 10 '14 at 11:09

answered Apr 10 '14 at 10:48

Rahul

76,197
13
71
125

Get a Substring in SQL between two characters and remove all white spaces

3 Answers3