Joining part of a field to another table

Posted on 2007-04-03
Last Modified: 2010-03-19
Dear Experts

I have two tables

Document_Filename (i.e. nick.gif)


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

Question by:nkewney
  • 2
  • 2
LVL 50

Expert Comment

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...
LVL 50

Accepted Solution

Lowfatspread earned 500 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 '%.' + + ' %'  
LVL 11

Expert Comment

ID: 18847514

You can use this function to extract file extension:
It returns substring after the last . (dot)

I hope this can help you

Best Regards
LVL 11

Expert Comment

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

Correct me if im wrong.
LVL 11

Expert Comment

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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

929 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now