String problems - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

String problems

Author

Topic

lemondash
Posting Yak Master

159 Posts

Posted - 2007-02-05 : 07:16:15

Afternoon all,

I was wondering if anybody could help me with a problem I’m a having.

I have a table which has around 177000 rows and inside is just one column of text, now inside the text is has a date.

An example of a column is [“lot of text here” Issue Date 25 January 2000, “lot text here”] – if it helps it has a comma after the date.

How can I go though the rows taking out the date and putting it in its own column? And then deleting it from the original column. But I do want to put the date on the same row it came from.

Any ideas would be great.

I don't like !!!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-05 : 07:29:07

what is the datatype of the column ?

lemondash
Posting Yak Master

159 Posts

Posted - 2007-02-05 : 07:34:05

The column the date is coming from is nVarChar and the datatype the date is going to is smalltimeanddate.

Hope that helps.

I don't like !!!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-05 : 07:39:58

1. Is there any key word to identify the start of the string for date ? like 'Issue Date' ?
2. is the date within the text always the same format ?

lemondash
Posting Yak Master

159 Posts

Posted - 2007-02-05 : 07:59:56

Yep Issuedate is the key word as the start of the date and it is always in the same format and always end's with a comma.

Issuedate 28 January 2000
Issuedate 17 September 1999
Issuedate 17 September 1999

I don't like !!!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-05 : 08:25:34

[code]declare @t table (data nvarchar(2000))

insert @t
select 'erew Issuedate 28 January 2000, gtdfgdf' union all
select 'wer w Issuedate 17 September 1999, weterw' union all
select 'ewr eIssuedate 17 September 1999, wrere'

select data,
'_' + substring(data, issuepos + 10, commapos - issuepos - 6) + '_',
cast(substring(data, issuepos + 10, commapos - issuepos - 6) as datetime)
from (
select data,
patindex('%issuedate %', data) as issuepos,
patindex('%[0-9][0-9][0-9][0-9], %', data) as commapos
from @t
) as d[/code]

Peter Larsson
Helsingborg, Sweden

lemondash
Posting Yak Master

159 Posts

Posted - 2007-02-05 : 08:42:45

oops maybe my mistake for not making it clear, the date format always stays the same. But the dates are all different.

I don't like !!!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-05 : 08:44:47

I don't like you give our suggestions zero attention...

Peter Larsson
Helsingborg, Sweden

lemondash
Posting Yak Master

159 Posts

Posted - 2007-02-05 : 08:48:18

Really am so sorry, just am really am stuggling and sometimes when i see queries like the one you wrote it confuse;s me more.

Please don't take offence to the phase 'I don't like' it's just from borat.

I don't like !!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources