parse string

Question

Post reply

parse string

smknox

Hall of Fame

Points: 3275
More actions
May 19, 2011 at 6:38 am

#240628

I'd like to "read" the date from the following string and add that to a datetime column:
Jane Smith 05 18 2011 mamm.pdf
There is no punctuation in the string and the month is always after the second space. What I'd like to do is pick out the 05, the 18 and the 2011, put them together, and then check to see if that is a valid date. Sometimes users are not consistent (put in three names or whatever). So check to see if we have a date. Once I have the date, I can get it into the right column.
THanks so much!!
Sam

Viewing 8 posts - 1 through 7 (of 7 total)

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

David Burrows SSC Guru Points: 65143 More actions · Answer 1

Providing that the month is the first 2 digits and the date is 10 chars long then this should work

ISDATE(REPLACE(SUBSTRING([column],PATINDEX('%[0-9][0-9]%',@x),10),' ','/'))

and

CAST(REPLACE(SUBSTRING([column],PATINDEX('%[0-9][0-9]%',@x),10),' ','/') as datetime)

Far away is close at hand in the images of elsewhere.
Anon.

calvo SSChampion Points: 12930 More actions · Answer 2

I like the patindex idea, I'll have to keep that one in the ol' toolbox.

Here's what I came up with

declare @x varchar(50) = 'Jane Smith 05 18 2011 mamm.pdf'

select cast(replace(substring(@x,

CHARINDEX(' ',@x,CHARINDEX(' ',@x,1)+1)+1,

10),' ','/') as datetime)

______________________________________________________________________________________________
Forum posting etiquette.[/url] Get your answers faster.

David Burrows SSC Guru Points: 65143 More actions · Answer 3

calvo (5/19/2011)
I like the patindex idea, I'll have to keep that one in the ol' toolbox.
Here's what I came up with
declare @x varchar(50) = 'Jane Smith 05 18 2011 mamm.pdf'
select cast(replace(substring(@x,
CHARINDEX(' ',@x,CHARINDEX(' ',@x,1)+1)+1,
10),' ','/') as datetime)

Except for "Sometimes users are not consistent (put in three names or whatever). "

Far away is close at hand in the images of elsewhere.
Anon.

calvo SSChampion Points: 12930 More actions · Answer 4

David Burrows (5/19/2011)
Except for "Sometimes users are not consistent (put in three names or whatever). "

Yep, I had that in mind while writing it and that's why I liked the PATINDEX method 🙂

I figured he'd run into a similar problem if someone had a number before the date in the filename (who knows). Thought I would post it just as an alternative method.

______________________________________________________________________________________________
Forum posting etiquette.[/url] Get your answers faster.

smknox Hall of Fame Points: 3275 More actions · Answer 5

REALLY appreciate the help!! Almost there.

Here is my statement so far:

SELECT d.ImageDetailID, d.ImageLink,

CASE WHEN

ISDATE(REPLACE(SUBSTRING(d.imageLink,PATINDEX('%[0-9][0-9]%',d.ImageLink),10),' ','/'))

THEN

CAST(REPLACE(SUBSTRING(d.ImageLink,PATINDEX('%[0-9][0-9]%',d.ImageLink),10),' ','/') as datetime) END

AS adate

FROM dbo.ImageDetail d

WHEREd.DateImage IS NULL

When I try to run get this error:

An expression of non-boolean type specified in a context where a condition is expected, near 'THEN'.

smknox Hall of Fame Points: 3275 More actions · Answer 6

figured it out! This works great:

ISDATE(REPLACE(SUBSTRING([column],PATINDEX('%[0-9][0-9]%',@x),10),' ','/')) =1

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 7

smknox (5/19/2011)
figured it out! This works great:
ISDATE(REPLACE(SUBSTRING([column],PATINDEX('%[0-9][0-9]%',@x),10),' ','/')) =1

I wouldn't put my trust in ISDATE the way you are. ISDATE returns a 1 on ANYTHING that can be converted to a date. For example...

SELECT ISDATE('2011')

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)