[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Varying Length Date

Posted on 2005-04-26
3
Medium Priority
?
246 Views
Last Modified: 2010-03-19
I'm trying to extract a date from a varying length string:

DECLARE sData as varchar(100)

Set sData = 'Some Data 20050118 0'
--can also be:
Set sData = 'Some Data 20050118 25643.98'

20050118 is what I'm trying to extract.

Sometimes there's a total at the far right, sometimes, it's just a zero. But the only thing that is certain is that between the date and total there's a blank. Any ideas?

Thanks!

0
Comment
Question by:Trancedified
  • 2
3 Comments
 
LVL 1

Author Comment

by:Trancedified
ID: 13869507
Oops:

Forgot the @ sign:

DECLARE @sData as varchar(100)

Set @sData = 'Some Data 20050118 0'
--can also be:
Set @sData = 'Some Data 20050118 25643.98'
0
 
LVL 28

Accepted Solution

by:
rafrancisco earned 2000 total points
ID: 13869697
declare @sdata varchar(200)
declare @date VARCHAR(8)

declare @startpos int
declare @endpos int

Set @sdata = 'Some Data 20050118 123.00'
set @endpos = len(@sdata) - charindex(' ', reverse(@sdata))
set @sdata = substring(@sdata, 1, @endpos)
set @sdata = substring(@sdata, len(@sdata) - charindex(' ', reverse(@sdata)) + 2, 8)
select @sdata
0
 
LVL 1

Author Comment

by:Trancedified
ID: 13869743
Right on, Thanks!
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

810 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