Solved

Joining part of a field to another table

Posted on 2007-04-03
5
153 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
  • 2
  • 2
5 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
Comment Utility
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 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Restore Procedure question 4 28
MS SQL 2016 from Database to Datawarehouse 6 34
Complex SQL 10 32
MySQL ERROR 1045 (28000) 2 36
CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

762 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

6 Experts available now in Live!

Get 1:1 Help Now