Parsing strings

Question

Parsing strings

Mark Green-451799

SSC-Addicted

Points: 490
More actions
April 28, 2008 at 8:52 am

#71492

I'm doing some text manipliation using T-SQL. The SELECT line I have is like:
REPLACE(REPLACE([Col032]+' 00:00:00.000', '2006', '2006-'),'2007','200
+7-') ,
This leaves me with entries in my table like:
2007-0329 00:00:00.000
I need them to take on the form:
2007-03-29 00:00:00.000
Update:
I wish to start with something like '20060211' and end up with :
'2006-02-11 00:00:00.000'
I only got so fasr with the replace function. i'll have a look at the function that steve pointed me to.
Any suggestions as to what is the best approach?

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736210 More actions · Answer 1

Use charindex/patindex to find the hyphen, then substring can be used to grab the stuff before where you need the space and after and combine them

substring (x,1,y) + '-' + substring(x, y+1, z)

If you need to determine if this is a 1 or 2 digit month, use substring to grab the values and compare them according to your logic.

Mark Green-451799 SSC-Addicted Points: 490 More actions · Answer 2

I don't need to determine if this is a one or two month string. They are all two digits to represent a month. Would it be better to use both the LTrim and RTrim functions in a single expression?

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

Have you tried simply casting your input string to a DATETIME format? it's fairly robust, and seems to pick up a LOT of formats....

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Mark Green-451799 SSC-Addicted Points: 490 More actions · Answer 4

It doesn't seem to recognise the format that I am working with (as detailed above).

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 5

Mark Green (4/28/2008)
It doesn't seem to recognise the format that I am working with.

You mind posting a few samples of the format? just to help find the right parse....

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Mark Green-451799 SSC-Addicted Points: 490 More actions · Answer 6

Mark Green-451799

SSC-Addicted

Points: 490

April 28, 2008 at 9:40 am

#807900

They are detailed above.

Mark Green-451799 SSC-Addicted Points: 490 More actions · Answer 7

Mark Green (4/28/2008)
I don't need to determine if this is a one or two month string. They are all two digits to represent a month. Would it be better to use both the LTrim and RTrim functions in a single expression?

I realise now that RTRIM and LTRIM is just for white space characters.

I am almost there with:

STUFF(REPLACE(REPLACE([Col032], '2006', '2006-'),'2007','2007-')+'-', 8, 13, ' 00:00:00.000'),

But that still misses out two digits representing the days.

daniloantunez SSC Veteran Points: 247 More actions · Answer 8

declare @a as varchar (8)

set @a = '20080428'

select cast( @a as datetime)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 9

Hmm...I must be missing something, cause CASE should handle that... That being said - assuming you're starting out with an 8-character string, then:

select replace(left([col032],4)+'-'+substring([col032],5,2)+'-'+substring([col032],7,2)+' 00:00:00.00.000','2006','2007')

would create a CHARACTER representation of the date. of course - if the middle part of the date is the DAY component (and not the month), you will likely get conversion errors if you try, in which case you'd want to swap the 2 SUBSTRING's around....

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Mark Green-451799 SSC-Addicted Points: 490 More actions · Answer 10

Mark Green-451799

SSC-Addicted

Points: 490

April 28, 2008 at 10:42 am

#807928

,'2006','2007')

.... what is that bit for?

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 11

Mark Green (4/28/2008)
,'2006','2007')
.... what is that bit for?

That's the end of the replace statement. You seemed to be trying to "increment" the year in your previous attempts. If you're not, then take the above AND the replace out.

EDIT: never mind - it's a slow caffeine day. Take out all of the replace altogether. Should just be:

SELECT left([col032],4)+'-'+substring([col032],5,2)+'-'+substring([col032],7,2)+' 00:00:00.00.000'

Assuming you want to use this value as a date, it would be better to figure out why the casting is failiing. Incrementing like the above could cause some invalid dates (like 2/29/2009 for example).

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Sergiy SSC Guru Points: 110208 More actions · Answer 12

Mark, you're facing the problem only because you don't like to read manuals and don't listen to people trying to help you.

You original format is standard ISO format, and of course it's handled by SQL Server date conversion.

If you would open topic CAST and CONVERT on BOL you'd find that it's style 112 for CONVERT function. So, you don't need to play with parsing strings, just use

CONVERT(datetime, String, 112)

Then you can add a year using system function DATEADD.

_____________
Code for TallyGenerator