Case statements - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Case statements

Author

Topic

rajikrishna
Starting Member

12 Posts

Posted - 2007-01-19 : 07:17:17

Hello,

I have a table with values like
F:\\foldername\subfolder\HowTo.doc
F:\\foldername\subfolder\HowTo.html
F:\\foldername\subfolder\HowTo.aspx
F:\\foldername\subfolder\Sample.ppt

While fetching records from this table If there is a *.doc file then I should display it as HowTo and also same is the case with *.ppt.

I need to use Case statments but I am not getting the proper syntax, to find is the value ends with *.doc or *.ppt I have used like '%.doc' but couldnt proceed further.

My desired output should be
HowTo
F:\\foldername\subfolder\HowTo.html
F:\\foldername\subfolder\HowTo.aspx
Sample

Thanks in advance

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-19 : 07:21:47

See this topic http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=77795

-- prepare sample data
declare	@tbl table (filename varchar(100))

insert	@tbl
select	'peso.txt' union all
select	'peso.asp' union all
select	'harsh.asp'

declare	@childtable table (extension varchar(10))

insert	@childtable
select	'.txt' union all
select	'.mpg'

select	t.*
from	@tbl as t
where	exists (select null from @childtable as ct where t.filename like '%' + ct.extension)

Peter Larsson
Helsingborg, Sweden

rajikrishna
Starting Member

12 Posts

Posted - 2007-01-19 : 07:31:50

Hi Peso,

Thanks.I saw that topic but I dont have any doubt in finding out if its a .doc or .ppt but I am stammering with case statements.
I am not getting them right, never tried case stmts before (learning SQL now).

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-19 : 07:45:58

[code]-- prepare sample data
declare @f table (filename varchar(200))

insert @f
select 'F:\foldername\subfolder\HowTo.doc' union all
select 'F:\foldername\subfolder\HowTo.html' union all
select 'F:\foldername\subfolder\HowTo.aspx' union all
select 'F:\foldername\subfolder\Sample.ppt'

-- do the work
select filename,
case
when right(filename, ext) in ('.doc', '.ppt') then right(filename, sl - 1)
else filename
end
from (
select filename,
charindex('.', reverse(filename)) as ext,
charindex('\', reverse(filename)) as sl
from @f
) as d[/code]

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-19 : 07:50:35

This does the same thing without derived table

-- prepare sample data
declare	@f table (filename varchar(200))

insert	@f
select	'F:\foldername\subfolder\HowTo.doc' union all
select	'F:\foldername\subfolder\HowTo.html' union all
select	'F:\foldername\subfolder\HowTo.aspx' union all
select	'F:\HowToaspx' union all
select	'F:\foldername\subfolder\Sample.ppt'

-- do the work
select	filename,
	case
		when right(filename, charindex('.', reverse(filename))) in ('.doc', '.ppt') then right(filename, charindex('\', reverse(filename)) - 1)
		else filename
	end
from	@f

Peter Larsson
Helsingborg, Sweden

rajikrishna
Starting Member

12 Posts

Posted - 2007-01-19 : 08:08:47

Thank you Peso.

Could you please explain what
right(filename, charindex('\', reverse(filename) - 1)
deos.

In my table I am havin values like HowTo. doc
sometimes I get a space after the ".".
So I am thinking of using like operator.

Cant i never use
when right(filename, charindex('.', reverse(filename))) like ('%doc','%ppt') then right(filename, charindex('\', reverse(filename) - 1)??

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-19 : 08:13:19

No, not with LIKE operator.
If you have problem with spaces, you need to consult your programmer responsible for storing the filenames.

No, you will have to figure that out for yourself. How will you ever learn and improve your skills? All commands are documented in Books Online (which should be your best friend).

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources