Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

MS SQL substring a character

Posted on 2008-10-07
3
583 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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Query Syntax 17 43
performance query 4 32
sql server query 12 26
IIF in access query 19 25
As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

861 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