Help Please - pulling data from varchar

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

SQL Server 2000 Forums

SQL Server Development (2000)

Help Please - pulling data from varchar

Author

Topic

adrox
Starting Member

17 Posts

Posted - 2005-09-17 : 01:39:14

I have this table:

CREATE TABLE [dbo].[OpmDeadline] (
[LeagueID] [int] NOT NULL ,
[DeadLineOption] [smallint] NOT NULL ,
[Deadline] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ThisPeriodDeadline] [smalldatetime] NOT NULL

Record sample:

LeagueID DeadLineOption Deadline ThisPeriodDeadline
1169661 1 d00h00m05t0 9/22/2005 8:55:00 PM
1174186 1 d00h00m05t0 9/22/2005 8:55:00 PM
1174548 2 d1h01m00t1 9/22/2005 8:55:00 PM
1176539 1 d00h00m05t0 9/22/2005 8:55:00 PM
1176901 1 d00h00m05t0 9/22/2005 8:55:00 PM
1177987 2 d1h04m12t1 9/22/2005 8:55:00 PM
1179073 1 d00h00m05t0 9/22/2005 8:55:00 PM
1179435 2 d1h01m00t1 9/22/2005 8:55:00 PM
1180883 1 d00h00m05t0 9/22/2005 8:55:00 PM
1181245 1 d00h00m05t0 9/22/2005 8:55:00 PM

What I need to do is parse the varchar field into d,h,m,t and the values that come after it.
Meaning:
d00
h00
m05
t0

so the query would return results like
leagueID d h m t
1169661 00 00 05 0
1177987 1 04 12 1

Note the values after the letters can be 1 or 2 digits.

Any ideas on how this can be done?

Thanks.

Kristen
Test

22859 Posts

Posted - 2005-09-17 : 01:54:55

It would probably be easier to clean the data up:


-- Change d9 to d99
UPDATE	U
SET	Deadline = LEFT(Deadline, 1) + '0' + SUBSTRING(Deadline, 2, LEN(Deadline))
FROM	OpmDeadline U
WHERE	Deadline LIKE 'd[0-9]h%'

-- Change h9 to h99
UPDATE	U
SET	Deadline = LEFT(Deadline, 3) + '0' + SUBSTRING(Deadline, 4, LEN(Deadline))
FROM	OpmDeadline U
WHERE	Deadline LIKE 'd[0-9][0-9]h[0-9]m%'

-- Change m9 to m99
UPDATE	U
SET	Deadline = LEFT(Deadline, 6) + '0' + SUBSTRING(Deadline, 7, LEN(Deadline))
FROM	OpmDeadline U
WHERE	Deadline LIKE 'd[0-9][0-9]h[0-9][0-9]m[0-9]t%'

and then SUBTRING it if you need it in separate columns

Kristen

adrox
Starting Member

17 Posts

Posted - 2005-09-17 : 12:08:41

The data clean up worked great!

Now how do I SUBTRING into different columns?

Or pull the data like this:

leagueID d h m t
1169661 00 00 05 0

What I am trying to accomplish is to take a date value and then
use date add to update the date by the data above. For example
if I have a date like 09/17/2005 9:00:00 PM

I would use the results from the query to get the value:

9/17/2005 9:05:00

I really could use any advise, I need to get this done asap.

Kristen
Test

22859 Posts

Posted - 2005-09-18 : 02:08:19

d = substring(Deadline, 2, 2),
h = substring(Deadline, 5, 2)

and so on. Have a look in the Books Online help for more details

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources