sql script help in using case statment with a select statement
Posted on 2014-12-19
i am new to sql scripting. Basically all I am trying to do is to output one column called 'Rev' based on the two case statements below. The scripting works for the most part. The only problem is that it outputs two columns called 'Rev'. i just need one column. So if the first case statement condition is not found, then move to the second case condition etc.
DECLARE @FullName VARCHAR(100)
SET @FullName = '07000496_C.F2'
case when @FullName like '%_%' and @FullName not like '%.%' then SUBSTRING(@FullName, CHARINDEX('_', @FullName) + 1, LEN(@FullName)) end AS Rev,
case when @FullName like '%_%' and @FullName like '%.%' then SUBSTRING(@FullName, CHARINDEX('.', @FullName) + 1, LEN(@FullName)) end AS Rev