Solved

sql FIELD CONVERT (SHORTEN)

Posted on 2011-09-14
3
295 Views
Last Modified: 2012-05-12
THIS IS PROBABLY SUPER EASY FOR ALL OF YOU.. BUT I'M STILL A NEWBIE AT SQL

i HAVE A COLUMN THAT CONTAINS DATA THAT LOOKS LIKE THIS.

666/WK22Reynolds/Jun 03, 2011/74/1/  

1057/6182011ims/Jun 22, 2011/302/1/1/ 1096-/217000-  -/3

I WANT TO CHANGE IT TO ONLY USE THE THE NUMBERS UP TO THE FIRST '/'

THEREFORE: return would be  

666  
1057
Can you help please?
0
Comment
Question by:Cree
3 Comments
 
LVL 18

Accepted Solution

by:
lludden earned 250 total points
ID: 36537185
SELECT SUBSTRING(Field1, 1,patindex('%/%',Field1)-1)
0
 
LVL 5

Expert Comment

by:zvytas
ID: 36537200
This will give you data you want, but as a string:

SELECT SUBSTRING(Column, 1, CHARINDEX('/', Column) - 1)

If you need an int, use the following:

SELECT CONVERT(int, SUBSTRING(Column, 1, CHARINDEX('/', Column) - 1))
0
 

Author Closing Comment

by:Cree
ID: 36537208
thanks.. you rock.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
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 to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

757 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

20 Experts available now in Live!

Get 1:1 Help Now