Solved

SQL Server: Can i use the distinct keyword on a portion of a column?

Posted on 2010-08-16
4
216 Views
Last Modified: 2012-05-10
I have a column in a table with data such as:

001/01
001/02
002/01
002/02

I'd like to return distinct values that occur in the table on the left hand side of the "/" for slash. So the query should return the values:

001
002

I appreciate it would suggest having seperated it in 2 columns but please presume that that is not possible at this moment. The application code could do this but wanted to know if SQL could query this information.

Many thanks =)
0
Comment
Question by:paddycobbett
  • 2
4 Comments
 
LVL 16

Expert Comment

by:vdr1620
ID: 33449746
Try the below SQL

SELECT Distinct SUBSTRING('001/01',1,PATINDEX('%/%','001/01')-1)
0
 
LVL 16

Accepted Solution

by:
vdr1620 earned 300 total points
ID: 33449754
you can change it accordingly to

SELECT Distinct SUBSTRING(COLUMNNAME,1,PATINDEX('%/%',COLUMNNAME)-1)
0
 
LVL 58

Assisted Solution

by:cyberkiwi
cyberkiwi earned 200 total points
ID: 33449815
The above may give you errors if no / exist in the string.

SELECT Distinct left(col1, charindex('/',col1+'/')-1)
from tbl
0
 
LVL 1

Author Closing Comment

by:paddycobbett
ID: 33461612
Thanks guys =)
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Select2 jquery help 9 46
Date conversion in sql server 2012 6 26
Caste datetime 2 24
DATETIMEOFFSET feature 1 0
In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how the fundamental information of how to create a table.

746 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

11 Experts available now in Live!

Get 1:1 Help Now