How to truncate the text in columns using SQL

Hi!

I have a table in SQL Server with the following columns:
ID (identificator)
FileName nvarchar(100)  ' ( e.g. file_name.ext)
FileExtension char(3)  '(e.g. pdf, exe, doc, etc)

How can I truncate the file name using SQL and leave only the name in the FileName column and shift 'ext' into FileExtension column?

LVL 1
ZaurbAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
update yourtable
   set FileExtension = right(FileName, 3)
    , FileName = left(FileName, len(FileName)-4)
0
 
ZaurbAuthor Commented:
Thanks! That's exactly what I wanted!
0
 
ZaurbAuthor Commented:
I know the question is closed, but how do I handle files in the same database with only 2 character extensions?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
here is the generic version to get the last '.' in the name:

update yourtable
   set FileExtension = right( FileName ,charindex('.', reverse(FileName ))-1)
    , FileName = left(FileName ,  len(FileName ) - charindex('.', reverse(FileName )))
0
 
ZaurbAuthor Commented:
Great! Thank you very much! Where can I get all this information so fast? Thanks again!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.