help with a string - 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

help with a string

Author

Topic

lemondash
Posting Yak Master

159 Posts

Posted - 2007-03-06 : 10:03:02

Afternoon all,

Any help here would be great havd a table quite big rougly with 177083 rows. Has one colunm and the data is string of text, but inside the text is a date which is built up like this 18 February 2000. With each date being different in each row but the format being the same. ie. 2 digits then a space then the month and the the year. Also some rows don't have a date at all so i would like to forget them ones.

What i would like to do is create a datetime colunm(which i can do), then take the date out of the row and place it in the datetime colunm.

Then delete it out of the original row.

Any help would be great.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-06 : 10:06:18

is the date in fixed position ? any way to identify the beginning of the date string ?

X002548
Not Just a Number

15586 Posts

Posted - 2007-03-06 : 10:07:31

Can I pray for you instead?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

lemondash
Posting Yak Master

159 Posts

Posted - 2007-03-06 : 10:10:10

Thanks for getting back to me.

No fixed position. Yes in front of the date its has the words 'Issuedate', so i can find the starting position by doing this:

select charindex('Issuedate',sdocument) +1
from tblEuroDocument

X002548
Not Just a Number

15586 Posts

Posted - 2007-03-06 : 10:35:29

quote:
Originally posted by lemondash

Thanks for getting back to me.

No fixed position. Yes in front of the date its has the words 'Issuedate', so i can find the starting position by doing this:

select charindex('Issuedate',sdocument) +1
from tblEuroDocument

OK, so that's a different story, Do you have some sample data?

And when you say text, you don't really mean text then, you mean varchar...

Can you post te DDL as well?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

lemondash
Posting Yak Master

159 Posts

Posted - 2007-03-06 : 10:53:15

Sorry For not making my self clear, hope this helps.

Ok this is my table.

tblEuroDocument

[ID] [int] IDENTITY(1,1) NOT NULL,
[sDocument] [nvarchar](500) NULL,
[sIssueDate] [smalldatetime] NULL

The date that i need is in the sDocument colunm, and the data in the colunm looks like this.

Company Name, ZAR 1795000000, IssueDate 21 February 2030, FRN, bond

Company Name, ZAR 71000000, IssueDate 16 August 2006, FRN, bond

Company Name , ZAR 71000000, IssueDate 17 May 2006, FRN, bond

Company Name, ZAR 3000000000, IssueDate 24 May 2020, FRN, bond

Company Name, ZAR 261030000, IssueDate 07 November 2010, FRN, bond

Company Name, ZAR 500000000, IssueDate 30 December 2015, FRN, bond

Company Name, ZAR 750000000, IssueDate 11 August 2011, 10%, bond

Company Name, ZAR 1500000000, IssueDate 20 September 2018, FRN, bond

And i would like to insert it in to the sIssueDate colunm on the same row it came from.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-06 : 11:12:56

With the help of this very fast string parser here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033

declare @s varchar(200)

set @s = 'Company Name, ZAR 1795000000, IssueDate 21 February 2030, FRN, bond'


select	@s,
	replace(dbo.fnParseString(3, ',', @s), ' issuedate ', ''),
	cast(replace(dbo.fnParseString(3, ',', @s), ' issuedate ', '') as datetime)

Peter Larsson
Helsingborg, Sweden

X002548
Not Just a Number

15586 Posts

Posted - 2007-03-06 : 11:31:05

[code]
CREATE TABLE tblEuroDocument99 (
[ID] [int] IDENTITY(1,1) NOT NULL,
[sDocument] [nvarchar](500) NULL,
[sIssueDate] [smalldatetime] NULL
)
GO

INSERT INTO tblEuroDocument99([sDocument])
SELECT 'Company Name, ZAR 1795000000, IssueDate 21 February 2030, FRN, bond' UNION ALL
SELECT 'Company Name, ZAR 71000000, IssueDate 16 August 2006, FRN, bond' UNION ALL
SELECT 'Company Name, ZAR 71000000, IssueDate 17 May 2006, FRN, bond' UNION ALL
SELECT 'Company Name, ZAR 3000000000, IssueDate 24 May 2020, FRN, bond' UNION ALL
SELECT 'Company Name, ZAR 261030000, IssueDate 07 November 2010, FRN, bond' UNION ALL
SELECT 'Company Name, ZAR 500000000, IssueDate 30 December 2015, FRN, bond' UNION ALL
SELECT 'Company Name, ZAR 750000000, IssueDate 11 August 2011, 10%, bond' UNION ALL
SELECT 'Company Name, ZAR 1500000000, IssueDate 20 September 2018, FRN, bond'
GO

UPDATE E SET sIssueDate =
CONVERT(datetime,
SUBSTRING(sDocument,
CHARINDEX('IssueDate',sDocument)+10
, CHARINDEX(',',sDocument,CHARINDEX('IssueDate',sDocument)+10) - (CHARINDEX('IssueDate',sDocument)+10)
))
FROM tblEuroDocument99 E
GO

SELECT * FROM tblEuroDocument99
GO

DROP TABLE tblEuroDocument99
GO

[/code]

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

lemondash
Posting Yak Master

159 Posts

Posted - 2007-03-06 : 11:52:01

Ok guys. Thank you for your help.

Have tried both options but get this as an error message.

Conversion failed when converting datetime from character string.

Any ideas ?

X002548
Not Just a Number

15586 Posts

Posted - 2007-03-06 : 12:01:18

What is your datetime setting set to?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

lemondash
Posting Yak Master

159 Posts

Posted - 2007-03-06 : 12:15:13

Thank you for your time on this. Hope this answers it.

I manually put one in.

set sIssueDate = '2000-02-16'

X002548
Not Just a Number

15586 Posts

Posted - 2007-03-06 : 13:00:22

From BOL

quote:

SET DATEFORMAT
Sets the order of the dateparts (month/day/year) for entering datetime or smalldatetime data.

Syntax
SET DATEFORMAT { format | @format_var }

Arguments
format | @format_var

Is the order of the dateparts. Can be either Unicode or DBCS converted to Unicode. Valid parameters include mdy, dmy, ymd, ydm, myd, and dym. The U.S. English default is mdy.

Remarks
This setting is used only in the interpretation of character strings as they are converted to date values. It has no effect on the display of date values.

The setting of SET DATEFORMAT is set at execute or run time and not at parse time.

I use the English Default. Do you have somethig different?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

yumyum113
Starting Member

31 Posts

Posted - 2007-03-06 : 15:35:09

Hi lemondash,

Perhaps you could try this solution...

First create this UDF 'getdateparam'

'getdatepara starts here
create function getdateparam(@str varchar(200))
returns datetime

as

begin
declare @ctr as int
declare @strdateparam varchar(50)
declare @dateparam as varchar(30)

set @ctr = 0
while @ctr<len(@str)
begin
if substring(@str,@ctr,9) = 'IssueDate'
begin
set @strdateparam = ''
while substring(@str,@ctr,1) <> ','
begin
set @strdateparam = @strdateparam + substring(@str,@ctr,1)
set @ctr = @ctr + 1
end
break
end
set @ctr = @ctr + 1
end

set @dateparam = substring(@strdateparam,11,2) + '-' + left(substring(@strdateparam,14,len(@strdateparam)),len(substring(@strdateparam,14,len(@strdateparam)))-5) +'-'+right(@strdateparam,4)

return (cast(@dateparam as datetime))
end
'getdateparam ends here

For testing try to run this script...

'test script starts here

create table #temp1
(
id int,
sDocument varchar(500),
issueDateTime smalldatetime
)

insert into #temp1 values(1,'Company Name, ZAR 1795000000, IssueDate 21 February 2030, FRN, bond',null)
insert into #temp1 values(2,'Company Name, ZAR 71000000, IssueDate 16 August 2006, FRN, bond',null)
insert into #temp1 values(3,'Company Name , ZAR 71000000, IssueDate 17 May 2006, FRN, bond',null)
insert into #temp1 values(4,'Company Name, ZAR 3000000000, IssueDate 24 May 2020, FRN, bond',null)
insert into #temp1 values(5,'Company Name, ZAR 261030000, IssueDate 07 November 2010, FRN, bond',null)
insert into #temp1 values(6,'Company Name, ZAR 500000000, IssueDate 30 December 2015, FRN, bond',null)
insert into #temp1 values(7,'Company Name, ZAR 750000000, IssueDate 11 August 2011, 10%, bond',null)
insert into #temp1 values(8,'Company Name, ZAR 1500000000, IssueDate 20 September 2018, FRN, bond',null)

update #temp1
set issueDateTime = dbo.getdateparam(sdocument)

select *
from #temp1

'test script ends here

Hope this helps.

-yumyum113

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources