Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 77
  • 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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