I have an issue while getting the file name with out extension from a column in SQL table like this.
The column name is [image] in dbo.emp table and the values are:
temp.jpg
test1.jpg
test1.jpeg
sample.jpg
sample1.gif
Now I want to get only temp, test1, sample, sample1 without the extension .jpg, .jpeg, etc…
DECLARE @x VARCHAR(32);
SET @x = 'temp.jpg';
SELECT SUBSTRING(@x, 1, CHARINDEX('.', @x)-1);
-- or SELECT LEFT(@x, CHARINDEX('.', @x)-1);
Result:
temp
This doesn't specify what to do if a . is not found, however. If you want to deal with that and return th whole string when no . is found:
DECLARE @x TABLE([image] VARCHAR(32));
INSERT @x SELECT 'temp.jpg' UNION ALL SELECT 'sample1.gif' UNION ALL SELECT 'foo';
SELECT SUBSTRING([image], 1, COALESCE(NULLIF(CHARINDEX('.', [image]),0)-1, 32)) FROM @x;
Results:
temp
x
foo
.. Also the starting at index 1 is just habit, since the first character = character 1, and that maps to how LEFT treats it.Another way:
left(fn, len(fn) - charindex('.', reverse(fn)))
E.g.
;with T (fn) as (
select '' union
select 'aaa' union
select 'aaa.bbb' union
select 'aaa.bbb.ccc'
)
select left(fn, len(fn) - charindex('.', reverse(fn))) from T
For
(No column name)
aaa
aaa
aaa.bbb
select
SUBSTRING([image], 0, charindex('.', [image], 0))
from
[table]
