Solved

MS SQL substring a character

Posted on 2008-10-07
3
581 Views
Last Modified: 2010-04-21
I viewed searched and found one of the questions that pertains to my issue "MS SQL substring a character"  The response did not work for me however i was hoping to get more assistance with a similar issue.

I am looking to pull the characters that happen on the left side of the first ':' for instance i could have data that looks like 12:31:01 or 13:20 or 1:00 or 3:00:09:00

In all of these cases i would like to pull out and use the 12,13,1, and 3

The code i attempted to modify was

substring(ltrim(rtrim(field)), 1, Charindex('$', ltrim(rtrim(field)))

I modified it to look like this:

substring(ltrim(rtrim([Time])), 1, Charindex(':', ltrim(rtrim([time])))

in which i received an error invalid or missing Expression.
0
Comment
Question by:JPLLaf
  • 2
3 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22664708
Try this:
SELECT LEFT('12:31:01', CHARINDEX(':', '12:31:01')-1)

Open in new window

0
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 22664727
Or simply this if you know there is at least one number up to max of 2 before the first ':'.
SELECT REPLACE(LEFT(LTrim(RTrim([Time])), 2), ':', '')

Open in new window

0
 

Author Closing Comment

by:JPLLaf
ID: 31504031
The second solution works great, for some reason the first one caused errors in previous ddate time formulas in my View.  Awesome work thanks!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
another query question 7 35
SQl Server 2008 R2 - Case When Null still equals NULL 10 27
SQL Union 20 44
Unable to save view in SSMS 21 59
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

920 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

15 Experts available now in Live!

Get 1:1 Help Now