?
Solved

Joining part of a field to another table

Posted on 2007-04-03
5
Medium Priority
?
171 Views
Last Modified: 2010-03-19
Dear Experts

I have two tables

Documents
=========
Document_Filename (i.e. nick.gif)

FileTypes
=========
FileType_Name
FileType_Description

How can I extract the extension description from FileTypes (FileType_Description) from Document_Filename (where available)

I'm looking for the following result from my query:

Document_Filename  |  TypeOfFile
nick.gif                        CompuServe GIF

Any help would be appreciated

Nick
0
Comment
Question by:nkewney
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 18847479
which database system the sql syntax varies...
obviously you should hold the filetype as a distinct column in your table and then this would be unnecessary...
0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 2000 total points
ID: 18847507
you could try ,..

select document_filename
    ,case when filetype_description is null then 'Unknown fileType'
                 else filetype_description end as typeoffile
 from documents as d
 left outer join filetypes as ft
  on d.document_filename + ' ' like '%.' + ft.name + ' %'  
0
 
LVL 11

Expert Comment

by:f_o_o_k_y
ID: 18847514
Hello

You can use this function to extract file extension:
RIGHT('File_name',charindex('.',reverse(File_name))-1)
It returns substring after the last . (dot)

I hope this can help you

Best Regards
FooKy
0
 
LVL 11

Expert Comment

by:f_o_o_k_y
ID: 18847525
Lowfatspread:
When i use Your code and use filename sth.gif.whatever.exe i can get wrong result

Correct me if im wrong.
0
 
LVL 11

Expert Comment

by:dready
ID: 18847560
If you are sure there is always just one '.' in the Document_Filename, you could use sqls substr and charindex functions.

SELECT SUBSTR(Document_Filename, charindex('.', Document_Filename))  from documents

should give  you the extension. If it has the . with it, you have to add a + 1 after the charindex.


The join could probably be done like:
SELECT documents.Document_Filename, FileTypes.FileType_Description from
documents left join FileTypes on FileTypes.FileType_Name = SUBSTR(Document_Filename, charindex('.', Document_Filename))

not sure, but give it a try
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question