CODEWITHSUNDEEP
sql-server
Sushil
Sushil

Reputation: 533

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

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

Upvotes: 0

Views: 2058

Answers (3)

TYY
TYY

Reputation: 2716

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

Upvotes: 1

Rahul
Rahul

Reputation: 77876

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

Upvotes: 0

shree.pat18
shree.pat18

Reputation: 21757

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

Upvotes: 0

Related Questions