• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3089
  • Last Modified:

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?

0
Zaurb
Asked:
Zaurb
  • 3
  • 2
1 Solution
 
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

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now