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

Duplicate + trim records SQL

Hi!
I have a sql and want 1 of the columns to return as 2 equal columns but one of them to be trimed. Is it possible?
Lets say you write "select files from images" and want the output to be "image1.jpg" in one of the columns and "image1" in the other.
0
Adam_Li
Asked:
Adam_Li
  • 4
  • 4
  • 2
  • +1
1 Solution
 
Lee SavidgeCommented:
so, this?

select files, replace(files, '.jpg', '') as filesnoext from images

Open in new window

0
 
Adam_LiAuthor Commented:
Quick answear! This works very well. Thanks
0
 
Pawan KumarDatabase ExpertCommented:
Are you looking for this

SELECT File, REPLACE(File, '.jpg', '') FileName from images

Open in new window

0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Pawan KumarDatabase ExpertCommented:
Great !
0
 
Lee SavidgeCommented:
You're welcome :)
0
 
awking00Commented:
You might have also used -
select files, left(files,len(files) - 4) as name from images
0
 
Pawan KumarDatabase ExpertCommented:
This will not work if your extension length is more than 3. E.g. jpeg
0
 
awking00Commented:
And the accepted answer wouldn't work if the extension was .jpeg (or any other extension) either :-)
0
 
awking00Commented:
Probably the safest way is to find the index of the ." and take the substring up until that index.
0
 
awking00Commented:
... of the "." ...
0
 
Pawan KumarDatabase ExpertCommented:
@awking00 - Yes you are correct. I have a solution for this.

@Author - if your extension can vary then use below rather than the accepted solution. To handle this you are can declare a variable and accept a value in there.

DECLARE @Ext AS VARCHAR(10) = '.jpg'

SELECT File, REPLACE(File, @Ext, '') FileName from images

Open in new window


Also please update the accepted solution.

Thanks!
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.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

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