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

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

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
paddycobbett
Asked:
paddycobbett
  • 2
2 Solutions
 
vdr1620Commented:
Try the below SQL

SELECT Distinct SUBSTRING('001/01',1,PATINDEX('%/%','001/01')-1)
0
 
vdr1620Commented:
you can change it accordingly to

SELECT Distinct SUBSTRING(COLUMNNAME,1,PATINDEX('%/%',COLUMNNAME)-1)
0
 
cyberkiwiCommented:
The above may give you errors if no / exist in the string.

SELECT Distinct left(col1, charindex('/',col1+'/')-1)
from tbl
0
 
paddycobbettAuthor Commented:
Thanks guys =)
0

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

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