Get everything after and before certain character in SQL Server

Solution 1:

use the following function

left(@test, charindex('/', @test) - 1)

Solution 2:

If you want to get this out of your table using SQL, take a look at the following functions that will help you: SUBSTRING and CHARINDEX. You can use those to trim your entries.

A possible query will look like this (where col is the name of the column that contains your image directories:

SELECT SUBSTRING(col, LEN(SUBSTRING(col, 0, LEN(col) - CHARINDEX ('/', col))) + 1, 
    LEN(col) - LEN(SUBSTRING(col, 0, LEN(col) - CHARINDEX ('/', col))) - LEN(SUBSTRING(
    col, CHARINDEX ('.', col), LEN(col))));

Bit of an ugly beast. It also depends on the standard format of 'dir/name.ext'.

Edit:
This one (inspired by praveen) is more generic and deals with extensions of different length:

SELECT SUBSTRING(col, LEN(LEFT(col, CHARINDEX ('/', col))) + 1, LEN(col) - LEN(LEFT(col, 
    CHARINDEX ('/', col))) - LEN(RIGHT(col, LEN(col) - CHARINDEX ('.', col))) - 1);

Solution 3:

Before

SELECT SUBSTRING(ParentBGBU,0,CHARINDEX('/',ParentBGBU,0)) FROM dbo.tblHCMMaster;

After

SELECT SUBSTRING(ParentBGBU,CHARINDEX('-',ParentBGBU)+1,LEN(ParentBGBU)) FROM dbo.tblHCMMaster